CREATE procedure order_pro @id int,@tot int output as set nocount on select @tot=sum(unitprice*quantity) from [order details] where orderid=@id return @tot go
USE pubs GO CREATE PROCEDURE myProc @outparm int OUTPUT @inparm int AS SELECT * FROM titles WHERE royalty > @inparm SELECT @outparm = COUNT (*) FROM TITLES WHERE royalty > @inparm IF (@outparm > 0) RETURN 0 ELSE RETURN 99 GO
Dim cn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset Dim fldloop As ADODB.Field Dim param1 As Parameter, param2 As Parameter, param3 As Parameter Dim provStr As String Dim royalty As Variant
Private Sub spStart()' Connect using the SQLOLEDB provider. cn.Provider = "sqloledb"' Specify connection string on Open method. provStr = "Server=MyServer;Database=pubs;Trusted_Connection=yes" cn.Open provStr' Set up a command object for the stored procedure. Set cmd.ActiveConnection = cn cmd.CommandText = "myProc" cmd.CommandType = adCmdStoredProc' Set up a return parameter. Set param1 = cmd.CreateParameter("Return", adInteger, adParamReturnValue) cmd.Parameters.Append param1
' Set up an output parameter. Set param2 = cmd.CreateParameter("Output", adInteger, adParamOutput) cmd.Parameters.Append param2
' Set up an input parameter. Set param3 = cmd.CreateParameter("Input", adInteger, adParamInput) cmd.Parameters.Append param3 royalty = Trim(InputBox("Enter royalty:")) param3.Value = royalty' Execute command, and loop through recordset, printing out rows. Set rs = cmd.ExecuteDim i As Integer While Not rs.EOF For Each fldloop In rs.Fields Debug.Print rs.Fields(i) i = i + 1 Next fldloop Debug.Print "" i = 0 rs.MoveNext Wend' Need to close recordset before getting return ' and output parameters. rs.CloseDebug.Print "Program ended with return code: " & Cmd(0) Debug.Print "Total rows satisfying condition: " & Cmd(1) cn.CloseEnd Sub
当你要从存储过程中返回表时 如果不设置 set nocount on 在查询分析器中是不会有任何问题的 但在VB中很有可能不能正确取得结果 你要做的就量修改你的存储过程:CREATE procedure order_pro @id int,@tot int output as set nocount on select @tot=sum(unitprice*quantity) from [order details] where orderid=@id
as
set nocount on
select @tot=sum(unitprice*quantity)
from [order details]
where orderid=@id
return @tot
go
GO
CREATE PROCEDURE myProc
@outparm int OUTPUT
@inparm int
AS
SELECT * FROM titles WHERE royalty > @inparm
SELECT @outparm = COUNT (*) FROM TITLES WHERE royalty > @inparm
IF (@outparm > 0)
RETURN 0
ELSE
RETURN 99
GO
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim fldloop As ADODB.Field
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter
Dim provStr As String
Dim royalty As Variant
Private Sub spStart()' Connect using the SQLOLEDB provider.
cn.Provider = "sqloledb"' Specify connection string on Open method.
provStr = "Server=MyServer;Database=pubs;Trusted_Connection=yes"
cn.Open provStr' Set up a command object for the stored procedure.
Set cmd.ActiveConnection = cn
cmd.CommandText = "myProc"
cmd.CommandType = adCmdStoredProc' Set up a return parameter.
Set param1 = cmd.CreateParameter("Return", adInteger, adParamReturnValue)
cmd.Parameters.Append param1
' Set up an output parameter.
Set param2 = cmd.CreateParameter("Output", adInteger, adParamOutput)
cmd.Parameters.Append param2
' Set up an input parameter.
Set param3 = cmd.CreateParameter("Input", adInteger, adParamInput)
cmd.Parameters.Append param3
royalty = Trim(InputBox("Enter royalty:"))
param3.Value = royalty' Execute command, and loop through recordset, printing out rows.
Set rs = cmd.ExecuteDim i As Integer
While Not rs.EOF
For Each fldloop In rs.Fields
Debug.Print rs.Fields(i)
i = i + 1
Next fldloop
Debug.Print ""
i = 0
rs.MoveNext
Wend' Need to close recordset before getting return
' and output parameters.
rs.CloseDebug.Print "Program ended with return code: " & Cmd(0)
Debug.Print "Total rows satisfying condition: " & Cmd(1)
cn.CloseEnd Sub
如果不设置
set nocount on
在查询分析器中是不会有任何问题的
但在VB中很有可能不能正确取得结果
你要做的就量修改你的存储过程:CREATE procedure order_pro @id int,@tot int output as
set nocount on
select @tot=sum(unitprice*quantity)
from [order details]
where orderid=@id