29、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’）

如：

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，在调用存储过程

的程序或批处理中，必须创建一个变量来接受输出参数的值。

注：虽然输出参数通常只用于从存储过程中返回值，但它们实际上是双向参数

（也就是说可以通过它向存储过程内部传递参数）

如：

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命令

如：

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

如：

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 服务器名.数据库名.对象名.过程名  参数赋值