SQL Server开发存储过程

SQL Server开发存储过程
 
存储过程是存储在存储过程名下的批处理，可被预编译。是服务器端代码。
 
具有如下优点：
 
1、存储过程是经过编译的，是执行查询和批处理的最快方式
 
2、在服务器端而不是桌面计算机上执行
 
3、存储过程是模块化的，提供了部署功能和修改代码简易途径
 
4、存储过程在数据库安全方面扮演了重要角色，可禁止用户直接访问表
 
一、管理存储过程
 
使用Create Alter Drop 来对存储过程进行创建、修改、删除
 
1、创建
 
格式：
 
Create Procedure 过程名(参数列表)
 
[with eccryption]
 
as
 
begin
 
..
 
return ..
 
end
 
2、修改
 
格式：除了关键字Create不一样，其他全一样。
 
注：如果要修改存储过程，使用Alter命令将优于删除存储过程，再重新创建存储过程，
 
因为后者在删除时，也删除了所有的权限设置。
 
3、删除
 
drop procdure 过程名
 
二、存储过程的编译
 
自动运行的，在首次执行时被编译并保存到内存中，当然也可以手工指定，
 
下次执行时重新编译。
 
exec sp_recompile 过程名
 
三、加密存储过程
 
通过在as 前加上with encryption
 
可用下面方法测试：sp_helptext 过程名
 
注：以sp_打头的一般都是系统存储过程。
 
四、向存储过程传递数据
 
存储过程可接受很多输入和输出参数
 
1、输入参数
 
通过在参数列表中添加参数实现，每个参数以@开头，并成为存储过程中的局部变量。
 
如：@name varchar(20)=‘XXM’
 
调用存储过程时，必须提供参数的值（除非有默认值，上面的参数就有默认值‘XXM’）
 
如：
 
[sql]
create procedure selectyuangong(@name varchar(20),@age int)  
  
with encryption  
  
as  
  
begin  
  
select * from yuangong where truename=@name and age=@age  
  
end  
调用：
 
exec selectyuangong @name=’XXM’,@age=20
 
或
 
exec selectyuangong ’XXM’,20
 
或
 
exec selectyuangong ’XXM’,@age=20
 
3种方式（但顺序需要与形参一致）
 
四、从存储过程中返回数据
 
SQL Server提供了4种从存储过程返回数据的方法，
 
1、通过select语句 2、通过raiserror
 
3、输出参数        4、通过return命令
 
1、输出参数
 
在参数列表中加入：@名称 类型 output
 
如：@name varchar(20) output
 
无论是创建存储过程还是调用存储过程时，都必须使用关键字output，在调用存储过程
 
的程序或批处理中，必须创建一个变量来接受输出参数的值。
 
注：虽然输出参数通常只用于从存储过程中返回值，但它们实际上是双向参数
 
（也就是说可以通过它向存储过程内部传递参数）
 
如：
 
[sql]
alter procedure selectyuangong(@name varchar(20),@age int output)  
  
with encryption  
  
as  
  
begin  
  
select @age=YEAR(GETDATE())-YEAR(birthday) from yuangong where truename=@name  
  
end  
  
go  
  
declare @bck_age int  
  
set @bck_age=-1  
  
exec selectyuangong '小三',@bck_age output  
  
print @bck_age  
 
 
2、使用Return命令
 
如：
 
[sql]
alter procedure selectyuangong(@name varchar(20),@age int output)  
  
with encryption  
  
as  
  
begin  
  
declare @count int  
  
select @count =count(*),@age=YEAR(GETDATE())-YEAR(birthday) from yuangong where truename=@name  group by birthday  
  
if(@count>0)  
  
return 1  
  
else  
  
return 0  
  
end  
  
go  
  
declare @bck_age int,@bck_count int  
  
set @bck_age=-1  
  
exec @bck_count=selectyuangong '小三',@bck_age output  
  
print @bck_age  
  
print @bck_count  
 
 
注：一般使用返回值来指出运行成功还是失败，而不要使用它来返回实际数据
 
五、返回数据的途径及其适用范围
 
1、return与output都将数据返回给SQL Server中直接调用存储过程的程序
 
或批处理
 
2、raiserror和select语句将数据直接返回给最终用户的客户端应用程序
 
注：对于每个返回的记录集，SQL Server在默认情况下，都将发送一条消息
 
，指出影响的行，但是影响性能，所以一般在存储过程开头加上
 
：set nocount on
 
如：
 
[sql]
alter procedure selectyuangong(@name varchar(20),@age int output)  
  
with encryption  
  
as  
  
begin  
  
set nocount on  
  
declare @count int  
  
select @count =count(*),@age=YEAR(GETDATE())-YEAR(birthday) from yuangong where truename=@name  group by birthday  
  
if(@count>0)  
  
return 1  
  
else  
  
return 0  
  
end  
 
六、查询中使用存储过程
 
1、使用openquery（）来调用存储过程--这属于分布式调用，也就是不同服务器之间
 
如：
 
Select * from OpenQuery(XXM-PC,’exec  selectyuangong @name=’XXM’’)
 
这句代码的意思是调用服务器XXM-PC执行selectyuangong存储过程，一般由远程
 
服务器实现。
 
2、在本地执行远程服务器的存储过程
 
exec 服务器名.数据库名.对象名.过程名  参数赋值