首页 > 代码库 > SQL Server存储过程简介
SQL Server存储过程简介
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 StudentInfo4 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,1010 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 int10 exec @sum=pr_例七11 print @sum12 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_例七_310 print @n11 ----执行存储过程时会出现以下的错误提示: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 @n10 11 go
-----临时存储过程--------
1 create proc #临时存储过程2 as3 select * from StudentInfo4 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 int11 as 12 begin13 begin tran--开启事务14 declare @errorSum int--定义一个变量存储错误代号15 set @errorSum=0--初始值为016 17 declare @tempCount int18 select @tempCount=count(*) from takeout_setting where brand_id=@takeoutid19 --判断有没有数据，有修改，没有增加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+@@ERROR25 end26 else--如果表中有数据，就修改数据27 begin28 update takeout_setting29 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=@payment37 where brand_id=@takeoutid38 set @errorSum=@errorSum+@@ERROR39 end40 if @errorSum<>041 begin42 rollback tran43 return 044 end45 else46 begin47 commit tran48 return 149 end50 end51 -----------------------------------52 begin tran53 declare @errorSum int54 set @errorSum=055 declare @a int56 set @a=157 declare @b int58 set @b=359 declare @c int60 set @c=10061 set @c=@b/@a62 set @errorSum=@errorSum+@@ERROR63 if(@errorSum<>0)64 begin65 rollback tran66 end67 else68 begin 69 commit tran70 end71 print @c72 print @errorSum
SQL Server存储过程简介