sqlserver存储过程语法详解
存储过程就是作为可执⾏对象存放在数据库中的⼀个或多个SQL命令。
定义总是很抽象。存储过程其实就是能完成⼀定操作的⼀组SQL语句，只不过这组语句是放在数据库中的(这⾥我们只谈SQL Server)。如果我们通过创建存储过程以及在ASP中调⽤存储过程，就可以避免将SQL语句同ASP代码混杂在⼀起。这样做的好处⾄少有三个：
第⼀、⼤⼤提⾼效率。存储过程本⾝的执⾏速度⾮常快，⽽且，调⽤存储过程可以⼤⼤减少同数据库的交互次数。
第⼆、提⾼安全性。假如将SQL语句混合在ASP代码中，⼀旦代码失密，同时也就意味着库结构失密。
第三、有利于SQL语句的重⽤。
在ASP中，⼀般通过command对象调⽤存储过程，根据不同情况，本⽂也介绍其它调⽤⽅法。为了⽅便说明，根据存储过程的输⼊输出，作以下简单分类：
1. 只返回单⼀记录集的存储过程
假设有以下存储过程(本⽂的⽬的不在于讲述T-SQL语法，所以存储过程只给出代码，不作说明)：
复制代码代码如下:
/*SP1*/
CREATE UserList
as
set nocount on
begin
select * from dbo.[userinfo]
end
go
以上存储过程取得userinfo表中的所有记录，返回⼀个记录集。通过command对象调⽤该存储过程的ASP代码如下:
复制代码代码如下:
'**通过Command对象调⽤存储过程**
DIM MyComm,MyRst
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr是数据库连接字串
MyComm.CommandText = "getUserList" '指定存储过程名
MyComm.CommandType = 4 '表明这是⼀个存储过程
MyComm.Prepared = true '要求将SQL命令先⾏编译
Set MyRst = MyComm.Execute
Set MyComm = Nothing
存储过程取得的记录集赋给MyRst，接下来，可以对MyRst进⾏操作。
在以上代码中，CommandType属性表明请求的类型，取值及说明如下：
-
1 表明CommandText参数的类型⽆法确定
1表明CommandText是⼀般的命令类型
2表明CommandText参数是⼀个存在的表名称
4表明CommandText参数是⼀个存储过程的名称
还可以通过Connection对象或Recordset对象调⽤存储过程，⽅法分别如下：
复制代码代码如下:
'**通过Connection对象调⽤存储过程**
DIM MyConn,MyRst
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.open MyConStr'MyConStr是数据库连接字串
Set MyRst = MyConn.Execute("getUserList",0,4) '最后⼀个参断含义同CommandType
Set MyConn = Nothing
'**通过Recordset对象调⽤存储过程**
DIM MyRst
Set MyRst = Server.CreateObject("ADODB.Recordset")
MyRst.open "getUserList",MyConStr,0,1,4
'MyConStr是数据库连接字串，最后⼀个参断含义与CommandType相同
2. 没有输⼊输出的存储过程
请看以下存储过程：
复制代码代码如下:
/*SP2*/
CREATE PROCEDURE dbo.delUserAll
as
set nocount on
begin
delete from dbo.[userinfo]
end
go
该存储过程删去userinfo表中的所有记录，没有任何输⼊及输出，调⽤⽅法与上⾯讲过的基本相同，只是不⽤取得记录集：
复制代码代码如下:
'**通过Command对象调⽤存储过程**
DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr是数据库连接字串
MyComm.CommandText = "delUserAll" '指定存储过程名
MyComm.CommandType = 4 '表明这是⼀个存储过程
MyComm.Prepared = true '要求将SQL命令先⾏编译
MyComm.Execute '此处不必再取得记录集
Set MyComm = Nothing
当然也可通过Connection对象或Recordset对象调⽤此类存储过程，不过建⽴Recordset对象是为了取得记录集，在没有返回记录集的情况下，还是利⽤Command对象吧。
3. 有返回值的存储过程
在进⾏类似SP2的操作时，应充分利⽤SQL Server强⼤的事务处理功能，以维护数据的⼀致性。并且，我们可能需要存储过程返回执⾏情况，为此，将SP2修改如下：
复制代码代码如下:
/*SP3*/
CREATE PROCEDURE dbo.delUserAll
as
set nocount on
begin
BEGIN TRANSACTION
delete from dbo.[userinfo]
IF @@error=0
begin
COMMIT TRANSACTION
return 1
end
ELSE
begin
ROLLBACK TRANSACTION
return 0
end
return
end
go
以上存储过程，在delete顺利执⾏时，返回1，否则返回0，并进⾏回滚操作。为了在ASP中取得返回值，需要利⽤Parameters集合来声明参数：
'**调⽤带有返回值的存储过程并取得返回值**
DIM MyComm,MyPara
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr是数据库连接字串
MyComm.CommandText = "delUserAll" '指定存储过程名
MyComm.CommandType = 4 '表明这是⼀个存储过程
MyComm.Prepared = true '要求将SQL命令先⾏编译
'声明返回值
Set Mypara = MyComm.CreateParameter("RETURN",2,4)
MyComm.Parameters.Append MyPara
MyComm.Execute
'取得返回值
DIM retValue
retValue = MyComm(0) '或retValue = MyComm.Parameters(0)
sql存储过程实例Set MyComm = Nothing
在MyComm.CreateParameter("RETURN",2,4)中，各参数的含义如下：
第⼀个参数("RETURE")为参数名。参数名可以任意设定，但⼀般应与存储过程中声明的参数名相同。此处是返回值，我习惯上设为"RETURE"；
第⼆个参数(2)，表明该参数的数据类型，具体的类型代码请参阅ADO参考，以下给出常⽤的类型代码：
复制代码代码如下:
adBigInt: 20 ;
adBinary : 128 ;
adBoolean: 11 ;
adChar: 129 ;
adDBTimeStamp: 135 ;
adEmpty: 0 ;
adInteger: 3 ;
adSmallInt: 2 ;
adTinyInt: 16 ;
adVarChar: 200 ;
对于返回值，只能取整形，且-1到-99为保留值；
第三个参数(4)，表明参数的性质，此处4表明这是⼀个返回值。此参数取值的说明如下：
0 : 类型⽆法确定； 1: 输⼊参数；2: 输⼊参数；3：输⼊或输出参数；4: 返回值
以上给出的ASP代码，应该说是完整的代码，也即最复杂的代码，其实
复制代码代码如下:
Set Mypara = MyComm.CreateParameter("RETURN",2,4)
MyComm.Parameters.Append MyPara
可以简化为
复制代码代码如下:
MyComm.Parameters.Append MyComm.CreateParameter("RETURN",2,4)
甚⾄还可以继续简化，稍后会做说明。
对于带参数的存储过程，只能使⽤Command对象调⽤(也有资料说可通过Connection对象或Recordset对象调⽤，但我没有试成过)。
4. 有输⼊参数和输出参数的存储过程
返回值其实是⼀种特殊的输出参数。在⼤多数情况下，我们⽤到的是同时有输⼊及输出参数的存储过程，⽐如我们想取得⽤户信息表中，某ID⽤户的⽤户名，这时候，有⼀个输⼊参数----⽤户ID，和⼀个输出参数----⽤户名。实现这⼀功能的存储过程如下：
/
*SP4*/
CREATE UserName @UserID int,
@UserName varchar(40) output
as
set nocount on
begin
if @UserID is null return
select @UserName=username
from dbo.[userinfo]
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论