存储过程就是把一个或多个T-SQL语句组合到一个逻辑单元中，在SQL Server数据库中保存为一个对象。在创建完成后，定义会保存在sys.sql_modules系统目录视图中。
存储过程的有点：
1、存储过程是在数据层汇集的T-SQL代码，可以促进代码的重复使用，同时有助于调试、寻找bug。也就是说这些代码就存放在数据库里，而不是嵌入在应用程序中的SQL代码，再由这些代码连接起来，最后发送到服务器上来执行的。
2、存储过程相对于大的即席查询，能够减少网络流量。
3、存储过程则更安全，因为内嵌的即席查询容易进行SQL注入。
4、相对于函数、视图，存储过程能使用流控制、临时表、表变量等。
5、可以淡化数据获取的方法。一旦底层的表有变化，不用去修改嵌入到应用程序中的sql代码，只要修改存储过程就可以了。
6、存储过程的查询响应时间比较稳定。存储过程提供了更加可靠的查询计划缓存，因此可以重用，而即席查询完全依赖于执行计划被缓存的环境（语句中的参数）。
另外，需要注意的：'CREATE/ALTER PROCEDURE'不允许将数据库名称指定为对象名前缀
--1.1创建无参数存储过程
--这个存储过程的定义很像视图定义
create procedure dbo.usp_wct
as
select wcId,
wcV,
wcDate
from wcT
go
--1.2执行存储过程
exec dbo.usp_wct
--2.1创建带参数的存储过程
create procedure dbo.usp_param_wct
(@wcId bigint =1, --默认值为1
@wcDate datetime =null) --默认值为null
as
select wcV
from dbo.wcT
where wcId = @wcId
and isnull(@wcDate,1)=case when @wcdate is null
then 1
else wcDate
end
--返回状态值,一般不用于返回计算结果,而是用来返回表明存储过程执行的状态
return 1
go
--2.2.1调用存储过程
exec dbo.usp_param_wct '1','2012-07-01'
--2.2.2调用存储过程,带参数名
exec dbo.usp_param_wct @wcid='1',
@wcdate='2012-07-01'
--2.2.3调用存储过程,带参数名,只输入一个参数值,另一个会使用默认值
exec dbo.usp_param_wct @wcid='1'
--2.2.4调用存储过程,带参数名,不输入参数,2个参数都会使用默认值
exec dbo.usp_param_wct
--3.1创建带output参数的存储过程
create procedure dbo.usp_output_param_wct
(@wcId bigint =1, --默认值为1
@wcDate datetime =null, --默认值为null
@wcV varchar(50) output) --输出参数,用来返回计算结果
as
select @wcV = wcV
from dbo.wcT
where wcId = @wcId
and isnull(@wcDate,1)=case when @wcdate is null
then 1
else wcDate
end
--返回状态值,一般不用于返回计算结果,而是用来返回表明存储过程执行的状态
return 1
go
--3.2调用存储过程
declare @output varchar(50)
exec dbo.usp_output_param_wct @wcId =1, --输入参数
--@wcDate = null,
@wcv = @output output --输出参数，存放返回值
print @output
--4.1修改存储过程
create procedure dbo.usp_output_param_wct
(@wcId bigint =1, --默认值为1
@wcDate datetime =null, --默认值为null
@wcV varchar(50) output) --输出参数,用来返回计算结果
with encryption --加密存储过程
as
select @wcV = wcV
from dbo.wcT
where wcId = @wcId
and isnull(@wcDate,1)=case when @wcdate is null
then 1
else wcDate
end
--返回状态值,一般不用于返回计算结果,而是用来返回表明存储过程执行的状态
return 1
go
--4.2查看定义
select object_name(object_id),
definition --显示为NULL
from sys.sql_modules
where object_id = object_id('dbo.usp_output_param_wct')
--5.在SQL Server启动时自动执行存储过程
create procedure dbo.create_table
as
select * into wc.dbo.wcX
from wc.dbo.wcT
go
--5.1设置为true,在SQL Server启动时自动执行存储过程
exec sp_procoption @procname = 'dbo.create_table',
@optionname = 'startup',
@optionvalue= 'true'
--5.2设置为false来禁止
--只有dbo拥有的master数据库中的对象才能更改启动设置。
use master
go
exec sp_procoption @procname = 'dbo.create_table',
@optionname = 'startup',
@optionvalue= 'false'
--6.1每次存储过程执行时都重新编译
--需要注意的是:当表、索引数据大量更新时，会进行语句级别的重新编译
create procedure dbo.usp_backupset_info
(@startDate datetime,
@finishDate datetime)
with recompile --不保存执行计划，每次都是重新编译
as
select SUM(backup_size)/1024/1024/1024 as 'GB'
from msdb.dbo.backupset
where backup_start_date >= @startDate
and backup_finish_date <= @finishDate
and type = 'D' --数据库备份，不是日志备份或差异备份
go
--6.2清空过程缓存
select *
from sys.dm_exec_cached_plans
dbcc freeproccache --清空过程缓存
--再次查询发现都已经清空
select *
from sys.dm_exec_cached_plans
--7.刷新存储过程，查看元数据
exec sp_refreshsqlmodule @name ='dbo.usp_output_param_wct'
select p.name, --存储过程名称
p.object_id,
pp.parameter_id,
pp.name, --参数名称
pp.default_value, --好像不起作用
t.name --参数列的类型
from sys.procedures p
inner join sys.parameters pp
on pp.object_id = p.object_id
inner join sys.types t
on t.system_type_id = pp.system_type_id
where p.object_id =965578478
--8.删除存储过程
DROP PROCEDURE dbo.usp_output_param_wct
存储过程的Execute as选项
--1.创建表
create table dbo.wcE
(vid int not null primary key clustered,
v int not null
)
insert into dbo.wcE
values(1,100),
(2,200),
(3,300)
--2.创建存储过程,存储过程的架构与存储过程中引用对象的架构一样
create procedure dbo.usp_delete_wcE
as
delete from dbo.wcE
go
--3.1创建登录名
create login wclogin with password = 'wclogin123'
--3.2创建数据库用户
create user wclogin
--4.授予用户执行存储过程的权限
grant exec on dbo.usp_delete_wcE to wclogin
grant connect to wclogin
/*====================================================
5.1打开另一个客户端,以wclogin登录名登进去,然后执行存储过程，
虽然这个用户没有访问存储过程中所引用表的权限,
但是由于存储过程的架构与存储过程中引用对象的架构一样,
同时由于存储过程中只能是insert，update，delete，select，
而不能是trancate等操作，这就是所有权链,所以没有报错.
特别需要注意的是:以上的所有权链对于动态的SQL是无效的.
======================================================*/
exec dbo.usp_delete_wcE
--5.2这里改为truncate后，会报错
alter procedure dbo.usp_delete_wcE
as
truncate table dbo.wcE
go
--6.1使用execute as owner指定存储过程的任何调用,
--都是在存储过程架构的拥有者的安全上下文运行的
--这时再次以wclogin登录就可以执行存储过程而不会报错
alter procedure dbo.usp_delete_wcE
with execute as owner
as
truncate table dbo.wcE
go
--6.2以执行用户的权限来执行存储过程
--在以wclogin登录执行时会报错
alter procedure dbo.usp_delete_wcE
with execute as caller
as
truncate table dbo.wcE
go
--6.3以创建或最近一次更新存储过程的用户的权限来执行存储过程,
--在以wclogin登录执行时会报错
alter procedure dbo.usp_delete_wcE
with execute as self
as
truncate table dbo.wcE
go
--6.4以指定的用户的权限来执行存储过程,
--在以wclogin登录执行时显然不会报错
alter procedure dbo.usp_delete_wcE
with execute as 'dbo'
as
truncate table dbo.wcE
go
--6.5这个存储过程用了动态的SQL语句
create procedure dbo.w
as
exec('select * from dbo.wcE');
go
grant exec on dbo.w to wclogin
--在客户端以wclogin登录,执行时会报错
--拒绝了对对象'wcE'(数据库'wc'，架构'dbo')的SELECT权限。
exec dbo.w
--1.函数
if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf')
drop function dbo.f_splitSTR
go
create function dbo.f_splitSTR
(
@s varchar(8000), --要分拆的字符串
@split varchar(10) --分隔字符
)
returns @re table( --要返回的临时表
col varchar(1000) --临时表中的列
)
as
begin
declare @len int
set @len = LEN(@split) --分隔符不一定就是一个字符，可能是2个字符
while CHARINDEX(@split,@s) >0
begin
insert into @re
values(left(@s,charindex(@split,@s) - 1))
set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'') --覆盖：字符串以及分隔符
end
insert into @re values(@s)
return --返回临时表
end
go
select * from dbo.f_splitSTR('123,456',',')
--2.存储过程
--创建类型
create type var_orgid as table
(
org_id numeric(10,0)
)
--创建存储过程
create proc usp_orgid
@orgid_table var_orgid readonly
as
select * from @orgid_table
go
--定义表变量
declare @orgid_table as var_orgid
insert into @orgid_table
values(123)
exec usp_orgid @orgid_table
sp_executesql存储过程，可以使动态语句重用，提高效率：
DECLARE @sumcount INT
DECLARE @tmpSQL NVARCHAR(1000)
DECLARE @TABLENAME VARCHAR(50)
SELECT @TABLENAME = ' sys.objects ' --表名变量赋值
SELECT @tmpSQL = 'select @sum = convert(varchar(50),count(object_id)) from ' + @TABLENAME
/*
参数1: 动态语句
参数2：定义的参数，这个参数就是在动态语句中引用到的变量@sum
参数3：指明了执行结果@sum的值，放到@sumcount参数里面，
这里需要特别注意的是，动态语句中的变量，和外面定义的变量，是不同的，
下面的语句，实际相当于：
declare @sum int
select @sum = convert(varchar(50),count(object_id))
from + 表名
所以，通过sp_executesql，可以把语句产生的结果输出到外面的变量，
这样我们才能知道输结果
*/
exec sp_executesql @tmpsql,
N'@sum int output',
@sumcount out
select @sumcount
try catch 错误处理
create proc proc_t(@i int)
as
select 5 * 1.0 / @i
go
--1.不会报错
begin try
begin tran
exec proc_t 1
select '执行成功'
commit tran
end try
begin catch
select '执行失败'
if @@trancount >0
rollback
end catch
--2.这次会报错,因为分母为0
begin try
begin tran
exec proc_t 0
select '执行成功'
commit tran
end try
begin catch
select '执行失败'
if @@trancount >0
rollback
end catch