SQL Server存储过程简介

浏览数：19 / 时间：2015年06月12日

1、存储过程的概念：

存储过程是一组预先写好的能实现某种功能的T-SQL程序，指定一个程序名并编译后将其存在SQL Server中，以后要实现该功能，可以调用这个程序来完成。

2、存储过程的分类：

系统存储过程、扩展存储过程、用户自定义存储过程。
（1）系统存储过程：一般以“sp_”为前缀，不要对其进行修改或删除。
（2）扩展存储过程：通常以“xp_”为前缀，尽量不要使用。
（3）用户自定义存储过程：可以输入参数、向客户端返货表格或结果、消息等，也可以返回输出参数。用户自定义存储过程有分为T-SQL存储过程和CLR存储过程两种。

3、在创建存储过程的时候，要确定三个组成部分：

（1）输入参数和输出参数
（2）在存储过程中执行的T-SQL语句
（3）返回的状态值，指明执行存储过程是成功还是失败

4、存储过程的参数：

在执行存储过程时，如果不指明参数名称，则按照存储过程定义的参数次序传递。

如果存储过程中定义了参数的默认值，并且放在最后，则传递参数时可以省略该参数值。

5、存储过程的返回值：

存储过程有三种不同的返回值：

（1）以“return n”的方式返回一个整数

（2）指定一个output的返回参数以返回值

（3）执行T-SQL语句返回数据集，例如select语句

6、创建存储过程中的注意事项：

（1）在存储过程中不能使用的一些语句：

（2）如果在存储过程中创建了临时表，只要存储过程退出了，临时表也就会被删除。

（3）在存储过程中，不但可以创建对象，还可以引用在该存储过程中已经定义好的对象。

（4）如果在一个存储过程中调用另一个存储过程，那么被调用的存储过程可以使用调用的存储过程里创建的对象，包括临时表。

（5）如果在存储过程中包含对远程SQL Server实例进行更改的T-SQL语句，一旦该语句执行后就不能回滚。

（6）存储过程中的参数最大数目为2100个，但存储过程中的局部变量的最大数目受内存的限制。

（7）存储过程最大可达128MB。

7、下面是一些例子：

------利用存储过程查询表-----

1 create proc pr_例一
2 as 
3 select * from StudentInfo
4 exec pr_例一
5 
6 go

-------带有参数的存储过程---------

 1 create proc pr_例二
 2 @stuId int
 3 as 
 4 begin
 5 select * from StudentInfo where id=@stuId
 6 end
 7 exec pr_例二 3
 8 exec pr_例二 @stuId=4
 9 
10 go

---------带有默认参数的存储过程----------

 1 create proc pr_例六
 2 @num int,
 3 @age int =18
 4 as
 5 select * from StudentInfo
 6 where num>@num and age>@age
 7 exec pr_例六 @num=3
 8 exec pr_例六 @num=3,@age=30
 9 exec pr_例六 3,10
10 
11 go

--------return n 方式的返回值---------

 1 create proc pr_例七
 2 as
 3 begin
 4 declare @返回值 int
 5 select @返回值=sum(age) from StudentInfo
 6 return @返回值
 7 end
 8 
 9 declare @sum int
10 exec @sum=pr_例七
11 print @sum
12 
13 go

---------output 方式的返回值----------

 1 create proc pr_例七_2
 2 @返回值 int output
 3 as
 4 select @返回值=sum(age) from StudentInfo
 5 
 6 declare @sum int
 7 exec pr_例七_2 @sum output
 8 print @sum
 9 
10 go

---------return n 方式的返回值只能返回整数-----------

 1 create proc pr_例七_3
 2 as
 3 declare @name varchar(10)
 4 select @name=name from StudentInfo
 5 where id=2
 6 return @name
 7 
 8 declare @n varchar(10)
 9 exec @n=pr_例七_3
10 print @n
11 ----执行存储过程时会出现以下的错误提示:
12 ----消息 245，级别 16，状态 1，过程 pr_例七_3，第 7 行
13 ----在将 varchar 值 ‘张四 ‘ 转换成数据类型 int 时失败。
14 
15 go

------output方式的返回值可以返回任何类型的数据------------

 1 create proc pr_例七_4
 2 @name varchar(10) output
 3 as
 4 select @name=name from StudentInfo
 5 where id=1
 6 
 7 declare @n varchar(10)
 8 exec pr_例七_4 @n output
 9 print @n
10 
11 go

-----临时存储过程--------

1 create proc #临时存储过程
2 as
3 select * from StudentInfo
4 
5 go

-----一个带有事务处理的存储过程------

 1 create proc pr_sample
 2 @money_limit int,
 3 @fee int,
 4 @free_money int,
 5 @area int,
 6 @time_start varchar(50),
 7 @time_end varchar(50),
 8 @id int,
 9 @takeoutid int,
10 @payment int
11 as 
12 begin
13 begin tran--开启事务
14 declare @errorSum int--定义一个变量存储错误代号
15 set @errorSum=0--初始值为0
16 
17 declare @tempCount int
18 select @tempCount=count(*) from takeout_setting where brand_id=@takeoutid
19 --判断有没有数据，有修改，没有增加
20 if(@tempCount=0)--如果表中没有数据，就新增数据
21 begin 
22 insert into takeout_setting(money_limit,fee,free_money,area,time_start,time_end,id,payment)
23 values(@money_limit,@fee,@free_money,@area,@time_start,@time_end,@id,@payment)
24 set @errorSum=@errorSum+@@ERROR
25 end
26 else--如果表中有数据，就修改数据
27 begin
28 update takeout_setting
29 set money_limit=@money_limit,
30 fee=@fee,
31 free_money=@free_money,
32 area=@area,
33 time_start=@time_start,
34 time_end=@time_end,
35 id=@id,
36 payment=@payment
37 where brand_id=@takeoutid
38 set @errorSum=@errorSum+@@ERROR
39 end
40 if @errorSum<>0
41 begin
42 rollback tran
43 return 0
44 end
45 else
46 begin
47 commit tran
48 return 1
49 end
50 end
51 -----------------------------------
52 begin tran
53 declare @errorSum int
54 set @errorSum=0
55 declare @a int
56 set @a=1
57 declare @b int
58 set @b=3
59 declare @c int
60 set @c=100
61 set @c=@b/@a
62 set @errorSum=@errorSum+@@ERROR
63 if(@errorSum<>0)
64 begin
65 rollback tran
66 end
67 else
68 begin 
69 commit tran
70 end
71 print @c
72 print @errorSum