SQLSERVER 存储过程使用说明书
引言
首先介绍一下什么是存储过程：存储过程就是将常用的或很复杂的工作，预先用 SQL 语句写好并用一个指定的名称存储起来，并且这样的语句是放在数据库中的，还可以根据条件执行不同 SQL 语句， 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时，只需调用 execute, 即可自动完成命令。
请大家先看一个小例子：
create proc query_book
as
select * from book
go
-- 调用存储过程
exec query_book请大家来了解一下存储过程的语法。
Create PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]一、参数简介
1 、 procedure_name
新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。
要创建局部临时过程，可以在 procedure_name 前面加一个编号符 (#procedure_name) ，要创建全局临时过程，可以在 procedure_name 前面加两个编号符 (##procedure_name) 。完整的名称（包括 # 或 ## ）不能超过 128 个字符。指定过程所有者的名称是可选的。
2 、 ;number
是可选的整数，用来对同名的过程分组，以便用一条 Drop PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1 、 orderproc;2 等。 Drop PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。
3 、 @parameter
过程中的参数。在 Create PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2100 个参数。
使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。
4 、 data_type
参数的数据类型。所有数据类型（包括 text 、 ntext 和 image ）均可以用作存储过程的参数。不过， cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor ，也必须同时指定 VARYING 和 OUTPUT 关键字。
说明：对于可以是 cursor 数据类型的输出参数，没有最大数目的限制。
5 、 VARYING
指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。
6 、 default
参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL 。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（ % 、 _ 、 [] 和 [^] ）。
7 、 OUTPUT
表明参数是返回参数。该选项的值可以返回给 EXEC[UTE] 。使用 OUTPUT 参数可将信息返回给调用过程。 Text 、 ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。
8 、 n
表示最多可以指定 2100 个参数的占位符。
9 、 {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
RECOMPILE 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。
ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 Create PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。
说明：在升级过程中， SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。
10 、 FOR REPLICATION
指定不能在订阅服务器上执行为复制创建的存储过程。 . 使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。
11 、 AS
指定过程要执行的操作。
12 、 sql_statement
过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。
13 、 n
是表示此过程可以包含多条 Transact-SQL 语句的占位符。
14 、注释
/* 和 */ 之间的为注释，可以包含一行和多行的说明文字。
15 、 其他说明
存储过程的最大大小为 128 MB 。
二、存储过程的优点都有哪些呢？
1. 存储过程 SQL 语句每执行一次就编译一次 , 所以使用存储过程可提高数据库执行速度。
2. 经常 SP 来封装数据库操作。当对数据库进行复杂操作时 ( 如对多个表进行 Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。可以极大的提高数据库的使用效率，减少程序的执行时间，这一点在较大数据量的数据库的操作中是非常重要的。在代码上看， SQL 语句和程序代码语句的分离，可以提高程序代码的可读性。
3. 存储
4. 安全性高 , 可设定只有某此用户才具有对指定存储过程的使用权存储过程的种类：
（ 1 ）系统存储过程：以 sp_ 开头 , 用来进行系统的各项设定 . 取得信息 . 相关管理工作 , 如 sp_help 就是取得指定对象的相关信息。
（ 2 ）扩展存储过程 以 XP_ 开头 , 用来调用操作系统提供的功能 exec master..xp_cmdshell 'ping 10.8.16.1'
（ 3 ）用户自定义的存储过程 , 这是我们所指的存储过程常用格式
模版： Create procedure procedue_name [@parameter data_type][output][with]{recompile|encryption} as sql_statement
解释： output ：表示此参数是可传回的
with {recompile|encryption} recompile: 表示每次执行此存储过程时都重新编译一次 ;encryption: 所创建的存储过程的内容会被加密。
三、实例讲解
实例 1
要求 1 ：查询表 bankMoney 的内容的存储过程
create procedure sp_query_bankMoney
as
select * from bankMoney
go
exec sp_query_bankMoney
注 * 在使用过程中只需要把中的 SQL 语句替换为存储过程名，就可以了很方便吧！
实例 2
加入一笔记录到表 bankMoney ，并查询此表中 userID= Zhangsan 的所有存款的总金额。
Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output
with encryption --------- 加密
as
insert bankMoney (id,userID,sex,Money) Values(@param1,@param2,@param3, @param4)
select @param5=sum(Money) from bankMoney where userID='Zhangsan'
go
在 SQL Server 查询分析器中执行该存储过程的方法是：
declare @total_price int
exec insert_bank '004','Zhangsan',' 男 ',100,@total_price output
print ' 总余额为 '+convert(varchar,@total_price)
go
在这里再啰嗦一下存储过程的 3 种传回值（方便正在看这个例子的朋友不用再去查看语法内容） :
1. 以 Return 传回整数
2. 以 output 格式传回参数
3.Recordset
传回值的区别 :
output 和 return 都可在批次程式中用变量接收 , 而 recordset 则传回到执行批次的客户端中。
实例 3 Select 语句的简单过程
下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。
USE pubs
IF EXISTS (Select name FROM sysobjects
Where name = 'au_info_all' AND type = 'P')
Drop PROCEDURE au_info_all
GO
Create PROCEDURE au_info_all
AS
Select au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GOau_info_all 存储过程可以通过以下方法执行：
EXECUTE au_info_all
-- or
EXEC au_info_all
如果该过程是批处理中的第一条语句，则可使用：
au_info_all
实例 4
Create PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
Select au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
Where au_fname = @firstname
AND au_lname = @lastname
GOau_info 存储过程可以通过以下方法执行：
EXECUTE au_info 'Dull', 'Ann'
-- or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
-- or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
-- or
EXEC au_info 'Dull', 'Ann'
-- or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- or
EXEC au_info @firstname = 'Ann', @lastname = 'Dull'如果该过程是批处理中的第一条语句，则可使用：
au_info 'Dull', 'Ann'
-- or
au_info @lastname = 'Dull', @firstname = 'Ann'
-- or
au_info @firstname = 'Ann', @lastname = 'Dull'实例 5
Create PROCEDURE au_info2
@lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
Select au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
Where au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO au_info2 存储过程可以用多种组合执行。下面只列出了部分组合：
EXECUTE au_info2
-- or
EXECUTE au_info2 'Wh%'
-- or
EXECUTE au_info2 @firstname = 'A%'
-- or
EXECUTE au_info2 '[CK]ars[OE]n'
-- or
EXECUTE au_info2 'Hunter', 'Sheryl'
-- or
EXECUTE au_info2 'H%', 'S%'四、系统存储过程
用户存储过程 :
系统存储过程 : SQL Server
系统存储过程可以在任意一个数据库中执行。创建并存放于系统数据库 master 中，并且名称以 sp_ 或者 xp_
sp_addtype
sp_configure
xp_sendmail
sp_stored_procedures
sp_help
sp_helptext
sp_rename
Sp_who:
sp_help
sp_depends
sp_helptext一个调用系统存储过程的例子：
exec sp_helptext query_book
五、注意事项 :
存储过程一般用来完成数据查询和数据处理操作，所以在存储过程中不可以使用创建数据库对象的语句，
CREATE TABLE ； CREATE VIEW ； CREATE DEFAULT
CREATE RULE ； CREATE TRIGGER ； CREATE PROCEDURE六、返回值和状态信息
无论什么时候执行存储过程，总要返回一个结果码，用以指示存储过程的执行状态。
如果存储过程执行成功，返回的结果码是 0
例：执行带参数的存储过程 ,
create proc show;3 ( @pno char(6) )
as
select * from person where Pno = @pno
exec show;3 4
例：
CREATE Procedure sp_getstu;1
AS
SELECT * FROM
例：带参数的存储过程 ,
CREATE proc sp_getstu;2 (@sage int)
AS
SELECT * FROM 学生 WHERE 年龄 > @sage例 : 带输出参数的存储过程 ,
CREATE proc sp_getstu;3 ( @name char(10) , @age int output )
AS
SELECT @age= 年龄 FROM 学生 WHERE 姓名 = @name
Declare @sage int
Exec sp_getstu;3 ' 张三 ',@sage
Print @sage
CREATE PROCedure sp_getstu;3 (@sage int =NULL )
AS
IF @sage IS NULL
BEGIN
PRINT ' 必须提供一个数值作参数！ '
RETURN 13
END
IF NOT EXISTS (SELECT * FROM student WHERE sage > @sage)
BEGIN
PRINT ' 没有满足条件的记录！ '
RETURN -103
END
SELECT * FROM student WHERE sage > @sage
RETURN 0
DECLARE @status int
EXECUTE @status=sp_getstu;3 22
print @status
七、存储过程中游标的使用
1
当 select
活动集： select
2
3
declare
declare 游标名 [scroll] cursor for select 语句 [for update [of 列表名 ]]
定义一个游标，使之对应一个 select
for update
open
open
fetch
fetch [next | prior | first | last | current | relative n | absolute m] 游标名 into [ 变量表 ]
close
关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行 open
close
deallocate
删除游标，以后不能再对该游标执行 open
deallocate
@@FETCH_STATUS
返回被 FETCH 语句执行的最后游标的状态 .
0 fetch
-1 fetch
-2
4例 : 查询电子商务系学生信息 , 性别为女输出为 female, 否则输出为 male
declare c1 cursor for select sno,sname,ssex from student where sdept='ec'
declare @sno char(10),@sname char(10),@ssex char(2)
Open c1
Fetch c1 into @sno,@sname,@ssex
While @@fetch_status==0
Begin
if @ssex=' 女 '
begin set @ssex='female' end
else
begin set @ssex='male' end
Select @sno,@sname ,@ssex
Fetch c1 into @sno,@sname,@ssex
end
例：
ALTER PROC [dbo].[dnt_UserRecoveryByUserName]
@username NVARCHAR(50)
AS
BEGIN
DECLARE @uid INT;
DECLARE @tid INT;
DECLARE @replies INT;
DECLARE @temp varchar(50);
SET @uid = (SELECT TOP(1) uid FROM dnt_users WHERE username = '@username');
SET @tid = 0;
SET @replies = 0;
UPDATE dnt_users SET accessmasks = 0 WHERE uid = @uid;
UPDATE dnt_userforum SET groupid = 5 WHERE groupid = 4 AND uid = @uid;
UPDATE dnt_posts SET invisible = 0 WHERE invisible = -1 AND posterid = @uid;
-- 定义一游标
DECLARE Ctemp CURSOR FOR SELECT tid FROM dnt_topics WHERE posterid = @uid FOR READ only -- FOR UPDATE
OPEN Ctemp
FETCH next FROM Ctemp INTO @tid;
WHILE (@@fetch_status = 0)
BEGIN
SET @replies = (SELECT COUNT(1) FROM dnt_posts WHERE tid = @tid AND layer > 0);
UPDATE dnt_topics SET replies = @replies WHERE posterid = @uid AND tid = @tid;
FETCH next FROM Ctemp INTO @tid;
END
CLOSE Ctemp;
DEALLOCATE Ctemp;
END
