/*SP1*/
CREATE PROCEDURE dbo.getUserList
as
set nocount on
begin
select * from dbo.[userinfo]
end
go
';**通过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
';**通过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相同
/*SP2*/
CREATE PROCEDURE dbo.delUserAll
as
set nocount on
begin
delete from dbo.[userinfo]
end
go
';**通过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
/*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
';**调用带有返回值的存储过程并取得返回值**
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)
Set MyComm = Nothing
adBigInt: 20 ;
adBinary : 128 ;
adBoolean: 11 ;
adChar: 129 ;
adDBTimeStamp: 135 ;
adEmpty: 0 ;
adInteger: 3 ;
adSmallInt: 2 ;
adTinyInt: 16 ;
adVarChar: 200 ;
Set Mypara = MyComm.CreateParameter("RETURN",2,4)
MyComm.Parameters.Append MyPara
可以简化为
MyComm.Parameters.Append MyComm.CreateParameter("RETURN",2,4)
/*SP4*/
CREATE PROCEDURE dbo.getUserName
@UserID int,
@UserName varchar(40) output
as
set nocount on
begin
if @UserID is null return
select @UserName=username
from dbo.[userinfo]
where userid=@UserID
return
end
go
';**调用带有输入输出参数的存储过程**
DIM MyComm,UserID,UserName
UserID = 1
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr ';MyConStr是数据库连接字串
MyComm.CommandText = "getUserName" ';指定存储过程名
MyComm.CommandType = 4 ';表明这是一个存储过程
MyComm.Prepared = true ';要求将SQL命令先行编译
';声明参数
MyComm.Parameters.append MyComm.CreateParameter("@UserID",3,1,4,UserID)
MyComm.Parameters.append MyComm.CreateParameter("@UserName",200,2,40)
MyComm.Execute
';取得出参
UserName = MyComm(1)
Set MyComm = Nothing
';**调用带有输入输出参数的存储过程(简化代码)**
DIM MyComm,UserID,UserName
UserID = 1
Set MyComm = Server.CreateObject("ADODB.Command")
with MyComm
.ActiveConnection = MyConStr ';MyConStr是数据库连接字串
.CommandText = "getUserName" ';指定存储过程名
.CommandType = 4 ';表明这是一个存储过程
.Prepared = true ';要求将SQL命令先行编译
.Parameters.append .CreateParameter("@UserID",3,1,4,UserID)
.Parameters.append .CreateParameter("@UserName",200,2,40)
.Execute
end with
UserName = MyComm(1)
Set MyComm = Nothing
';**多次调用同一存储过程**
DIM MyComm,UserID,UserName
UserName = ""
Set MyComm = Server.CreateObject("ADODB.Command")
for UserID = 1 to 10
with MyComm
.ActiveConnection = MyConStr ';MyConStr是数据库连接字串
.CommandText = "getUserName" ';指定存储过程名
.CommandType = 4 ';表明这是一个存储过程
.Prepared = true ';要求将SQL命令先行编译
if UserID = 1 then
.Parameters.append .CreateParameter("@UserID",3,1,4,UserID)
.Parameters.append .CreateParameter("@UserName",200,2,40)
.Execute
else
';重新给入参赋值(此时参数值不发生变化的入参以及出参不必重新声明)
.Parameters("@UserID") = UserID
.Execute
end if
end with
UserName = UserName + MyComm(1) + "," ';也许你喜欢用数组存储
next
Set MyComm = Nothing
欢迎光临 黑色海岸线论坛 (http://bbs.thysea.com/) | Powered by Discuz! 7.2 |