sqlserver创建、输入和优化存储过程等系列教程

存储过程

是由一个或多个Transact_SLQ 语句组成的一个组。

存储过程可以调用存储过程。

优点

减少了服务器、客户端的网络流量，更强的安全性，代码重复使用，更容易维护，改进性能。

1.创建存储过程

if object_id ('spu_01','p') is not null
drop procedure spu_01
create procedure spu_01
as 
begin 
select * from emp
end 
exec spu_01

2.带输入参数的存储过程

alter procedure  spu_01
@id int ,@num int
as 
begin
select * from emp  
where ID>@id  and  num <@num
end
exec spu_01 1,80

3.带通配符的存储过程

if object_id ('spu_02','p') is not null
drop  procedure spu_02

create procedure spu_02
@name varchar(50)
as 
begin
select *from emp
where FirstName like @name --- 输入参数使用通配符，要使用关键词like。 
end 

exec spu_02 'J%'

4.带书出参数的存储过程

create procedure spu_03 

@name  varchar(20) output,
@num int 
as
begin
select @name=FirstName from emp
where num=@num 
select @name as name
end 

declare @name varchar(20)
exec spu_03 @name output ,60

只显示一个值

如果该参数的是为多个则只显示最后一个值

5.带表值参数的存储过程

create type tp_name as table 
(
   id int,
   tpname varchar(50)
)

create procedure spu_05
@insert tp_name readonly
as
begin
insert into eemp 
select * from @insert
end

declare @insert as tp_name;
insert into  @insert 
select  1,'ix'
union all
select 2,'xi'
exec  spu_05 @insert 

6.加密、重新编译

使用 with encryption 对存储过程进行模糊处理

使用with recompile 重新编译过程

数据或结构进行了更改，重新编译过程会进行针对这些更改优化查询的计划，提高过程的处理性能。

对过程引用的基础表添加了过程可能从中受益的索引，可以强制在下次执行过程时对其重新编译。

alter procedure spu_name

@var int

with encryption ,recompile

7.存储过程的优化

set nocount on/off 开启或关闭显示受TSQL影响的行计数消息。 当过程中创建或引用数据库对象时使用架构名称，减少数据的库引擎解析对象名称所处理的时间。 避免函数包装在where 和join 自己中指定的列。这样会使列具有不确定性且禁止处理器使用索引。 避免在返回许多行的数据的select 语句中使用函数。因为标量行数必须应用与每一行，会降低性能。 避免使用select*。而是指定所需的列名称。

避免处理或返回过的多数据，尽可能在过程代码缩小结果的范围。 通过时用begin/end transaction 来使用显式事务并保留尽可能短的事务。更长的事务意味着更长的记录锁定和更高的死锁风险。 使用Transact—-SQL try 。。。catch进行过程内的错误处理。try。。。catch 可以封装整个Transact-SQL语句快。 在过程主题中对create table 或 alter table Transact-SQL语句引用的所有表列使用de。fault 关键字。这将禁止null 传递到不允许null值的列。 对于临时表中的每一列使用null或者not null。 使用union all 运算来代替union 或 or 运算符，除非存在针对非重复值的特定需要，union all 要求更少的处理开销.重复值不从结果中筛选出来。