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、下面是一些例子：

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

create proc pr_例一
as 
select * from StudentInfo
exec pr_例一

go

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

create proc pr_例二
@stuId int
as 
begin
select * from StudentInfo where id=@stuId
end
exec pr_例二 3
exec pr_例二 @stuId=4

go

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

create proc pr_例六
@num int,
@age int =18
as
select * from StudentInfo
where num>@num and age>@age
exec pr_例六 @num=3
exec pr_例六 @num=3,@age=30
exec pr_例六 3,10

go

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

create proc pr_例七
as
begin
declare @返回值 int
select @返回值=sum(age) from StudentInfo
return @返回值
end

declare @sum int
exec @sum=pr_例七
print @sum

go

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

create proc pr_例七_2
@返回值 int output
as
select @返回值=sum(age) from StudentInfo

declare @sum int
exec pr_例七_2 @sum output
print @sum

go

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

create proc pr_例七_3
as
declare @name varchar(10)
select @name=name from StudentInfo
where id=2
return @name

declare @n varchar(10)
exec @n=pr_例七_3
print @n
----执行存储过程时会出现以下的错误提示:
----消息 245，级别 16，状态 1，过程 pr_例七_3，第 7 行
----在将 varchar 值 '张四 ' 转换成数据类型 int 时失败。

go

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

create proc pr_例七_4
@name varchar(10) output
as
select @name=name from StudentInfo
where id=1

declare @n varchar(10)
exec pr_例七_4 @n output
print @n

go

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

create proc #临时存储过程
as
select * from StudentInfo

go

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

 

create proc pr_sample
@money_limit int,
@fee int,
@free_money int,
@area int,
@time_start varchar(50),
@time_end varchar(50),
@id int,
@takeoutid int,
@payment int
as 
begin
begin tran--开启事务
declare @errorSum int--定义一个变量存储错误代号
set @errorSum=0--初始值为0

declare @tempCount int
select @tempCount=count(*) from takeout_setting where brand_id=@takeoutid
--判断有没有数据，有修改，没有增加
if(@tempCount=0)--如果表中没有数据，就新增数据
begin 
insert into takeout_setting(money_limit,fee,free_money,area,time_start,time_end,id,payment)
values(@money_limit,@fee,@free_money,@area,@time_start,@time_end,@id,@payment)
set @errorSum=@errorSum+@@ERROR
end
else--如果表中有数据，就修改数据
begin
update takeout_setting
set money_limit=@money_limit,
fee=@fee,
free_money=@free_money,
area=@area,
time_start=@time_start,
time_end=@time_end,
id=@id,
payment=@payment
where brand_id=@takeoutid
set @errorSum=@errorSum+@@ERROR
end
if @errorSum<>0
begin
rollback tran
return 0
end
else
begin
commit tran
return 1
end
end
-----------------------------------
begin tran
declare @errorSum int
set @errorSum=0
declare @a int
set @a=1
declare @b int
set @b=3
declare @c int
set @c=100
set @c=@b/@a
set @errorSum=@errorSum+@@ERROR
if(@errorSum<>0)
begin
rollback tran
end
else
begin 
commit tran
end
print @c
print @errorSum