怎样在Sql server中创建、执行和删除存储过程

储存过程的分类：

1. 系统存储过程，存放在MASTER数据库中，以SP_前缀。

2. 用户自定义存储过程。

 

在SQL SERVER中创建储存过程语法：

Create proc[edure] procedure_name[;number]

[{@parameter data_type}

[varying][=default][output]

][,…n]

[with {recompile|encryption|encrypton,recomption}]

[for enplication]

As

Sql_statement[,…n]

参数:

整个语法中只有两个参数是必须的：procedure_name存储过程名称和sql_statement存储过程内容。

procedure_name：存储过程名称。

;number：对存储过程分组，“；数字”表示是一组存储过程，便于批量操作。

@parameter：参数名,命名必须用@开头；局部变量以@开头，全局变量以@@开头。

data_type：参数类型。

varying：指定作为输出参数的结果集。

Default：参数的默认值。

Output：表示为输出参数，返回值给execute/exe。

[with {recompile|encryption|encrypton,recomption}][for enplication]：这个我还不明白。。。

As：指定储存过程要执行的操作。

 

执行存储过程语法：

Exec[ute] 存储过程名 参数

 

实验一下，看看效果：

1.创建一组存储过程并且执行

create procedure procedure_a;1

as

select * from student

go

create procedure procedure_a;2

as

select * from student

go

执行创建效果截图：

图1创建一组存储过程

2.执行语句：

Execute procedure_a

效果截图：

图2执行存储过程效果截图

3.删除语句：

Drop procedure procedure_a

效果截图

图3删除一组储存过程效果截图

 

创建带输入参数的存储过程：输入参数是程序将参数值传递给存储过程。

创建一个带输入参数的存储过程，设置默认值，并且对输入参数的值进行判断。

Create procedure pro_1

@pro_1_a varchar(20)=null

As

If @pro_1_a is null

Print'请输入需要查询的所属院系：'

else

Select avg(成绩) from student where 所属院系=@pro_1_a

分别执行一下：

1.exec pro_1

效果截图：

图4执行不带输入参数的语句

2.exec pro_1 ‘计算机’

效果截图：

图5执行带输入参数的语句

哈哈，verygood,有进步啊。

 

创建带输出参数的存储过程：输出参数是存储过程将返回值传递给程序。

1.输出函数需要指定output作为标识

2.声明变量需要使用declare

3.给变量指定值需要使用select

来一个例子：如果一个数大于5，则计算他的阶乘。

Create procedure pro_2

/*声明输入输出参数*/

@in_x int,

@out_y int output

as

/*声明标量，并且赋值为1*/

Declare @x int,@y int

Select @x=1,@y=1

/*if语句判断输入值是否合法，使用while循环语句计算*/

If @in_x<=5

Print'请输入大于5的数：'

Else

While @x<=@in_x

Begin

Select @y=@y*@x

Select @x=@x+1

End

Select @out_y=@y

1.执行以上创建代码

图6创建带输入输出参数的存储过程截图

2.提供参数执行代码

Declare @out_sum int

Exec pro_2 6,@out_sum output

Select @out_sum as result

图7提供输入输出参数执行存储过程截图

 

创建多条语句的存储过程：一个存储过程中可以写入多条SQL语句，这些语句将被依次执行，可以实现多个功能。

 

删除存储过程delete及判断对象是否存在exists：

Delete procedure procedure_name

If exists(select子查询)：如果exists的参数查询为非空，则exists结果为true,否则为假。

存储过程作为一个对象将被保存在数据库的sysobjects表中，可以使用语句判断这个对象是否存在：select * from sysobjects where name=’procedure_name’

 

几个系统存储过程的使用：

Sp_name:重命名对象-- Sp_name object1,object2

Sp_depends:显示引用对象—sp_depends students

Sp_help:显示对象信息

Sp_helptext:显示对象的源代码