下面是存储过程, 库是SQL中的Pubs库:
CREATE procedure sp_PubsTest
-- 定义三个参数变量,注意第三个,特别标记是用于输出
@au_lname varchar (20),
@intID int,
@intIDOut int OUTPUT
AS
SELECT @intIDOut = @intID + 1
SELECT *
FROM authors
WHERE au_lname LIKE @au_lname + '%'
--直接返回一个值
RETURN @intID + 2
GO
调用如下:
1@ Language=VBScript
1
2Dim CmdSP
3Dim adoRS
4Dim adCmdSPStoredProc
5Dim adParamReturnValue
6Dim adParaminput
7Dim adParamOutput
8Dim adInteger
9Dim iVal
10Dim oVal
11Dim adoField
12Dim adVarChar
13adCmdSPStoredProc = 4
14adParamReturnValue = 4
15adParaminput = 1
16adParamOutput = 2
17adInteger = 3
18adVarChar = 200
19iVal = 5
20oVal = 3
21Set CmdSP = Server.CreateObject("ADODB.Command")
22CmdSP.ActiveConnection = "Driver={SQL Server};server=(local);Uid=sa;Pwd=123;Database=Pubs"
23CmdSP.CommandText = "sp_PubsTest"
24CmdSP.CommandType = adCmdSPStoredProc
25CmdSP.Parameters.Append CmdSP.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 4)
26CmdSP.Parameters.Append CmdSP.CreateParameter("@au_lname", adVarChar, adParaminput, 20, "M")
27CmdSP.Parameters.Append CmdSP.CreateParameter("@intID", adInteger, adParamInput, , iVal)
28CmdSP.Parameters.Append CmdSP.CreateParameter("@intIDOut", adInteger, adParamOutput, oVal)
29Set adoRS = CmdSP.Execute
30While Not adoRS.EOF
31For each adoField in adoRS.Fields
32Response.Write adoField.Name & "=" & adoField.Value & "
<br/>
1" & vbCRLF
2Next
3Response.Write "
<br/>
1"
2adoRS.MoveNext
3Wend
4Response.Write CmdSP.Parameters("@intIDOut").Value
5Response.Write CmdSP.Parameters("@RETURN_VALUE").Value
6Set adoRS = nothing
7Set CmdSP.ActiveConnection = nothing
8Set CmdSP = nothing
现在下面这两句输出都没有值!
Response.Write CmdSP.Parameters("@intIDOut").Value
Response.Write CmdSP.Parameters("@RETURN_VALUE").Value
请帮忙!现在没有分了请见谅!!
---------------------------------------------------------------
Set adoRS = CmdSP.Execute
改为
CmdSP.Execute()
---------------------------------------------------------------
1@ Language=VBScript
1
2Dim CmdSP
3Dim adoRS
4Dim adCmdSPStoredProc
5Dim adParamReturnValue
6Dim adParaminput
7Dim adParamOutput
8Dim adInteger
9Dim iVal
10Dim oVal
11Dim adoField
12Dim adVarChar
13adCmdSPStoredProc = 4
14adParamReturnValue = 4
15adParaminput = 1
16adParamOutput = 2
17adInteger = 3
18adVarChar = 200
19iVal = 5
20oVal = 3
21Set CmdSP = Server.CreateObject("ADODB.Command")
22CmdSP.ActiveConnection = "Driver={SQL Server};server=(local);Uid=sa;Pwd=123;Database=Pubs"
23CmdSP.CommandText = "sp_PubsTest"
24CmdSP.CommandType = adCmdSPStoredProc
25CmdSP.Parameters.Append CmdSP.CreateParameter("@RETURN_VALUE",adInteger,adParamReturnValue,,4)
26CmdSP.Parameters.Append CmdSP.CreateParameter("@au_lname",adVarChar, adParaminput, 20, "M")
27CmdSP.Parameters.Append CmdSP.CreateParameter("@intID",adInteger,adParamInput,,iVal)
28CmdSP.Parameters.Append CmdSP.CreateParameter("@intIDOut",adInteger,adParamOutput,,oVal)
29Set adoRS = CmdSP.Execute
30While Not adoRS.EOF
31For each adoField in adoRS.Fields
32Response.Write adoField.Name & "=" & adoField.Value & "
<br/>
1" & vbCRLF
2Next
3Response.Write "
<br/>
1"
2adoRS.MoveNext
3Wend
4adoRS.close()
5Set adoRS = nothing '须先关闭才能取到返回值与输出值!
6Response.Write "
<p>@intIDOut = "& CmdSP.Parameters("@intIDOut").Value & "</p>
1"
2Response.Write "
<p>Return value = " & CmdSP.Parameters("@RETURN_VALUE").Value & "</p>
1"
2
3Set CmdSP.ActiveConnection = nothing
4Set CmdSP = nothing
1