ADO and SQL Server Using Return Code and Output Parameters for Stored Procedures Stored procedures can contain input parameters, output parameters, and return values. You specify input parameters, output parameters, and return values for a stored procedure through the Parameter object. In the case of output parameters and return values, the values are not returned until the data of the Recordset object has been fetched completely or the Recordset has been closed. The following stored procedure contains one input parameter, one output parameter, and a return parameter. The procedure selects those rows in the titles table of the pubs database where the royalty percent paid to the author is greater than the amount entered by the user (the input parameter). The program returns the number of rows as the output variable. If the program returns any rows, a return code of 0 is issued; if no rows are returned, a return code of 99 is issued.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 GOAn ADO code program that executes the stored procedure myProc is shown here.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 SubThe following parameters are needed for the myProc stored procedure: A return parameter to hold the return value (0 or 99).The return parameter is created as a return type of parameter adParamReturnValue, and the data type is adInteger for integer. Because the return parameter is the first parameter added to the collection, its index value is zero, and it can be dereferenced through that index (for example, as Cmd(0)). An output parameter to hold the value of the count of the number of returned rows. The output parameter is created as adParamOuput for the output parameter type, and the data type is adInteger for integer. Because the output parameter is the second parameter added to the collection, its index value is 1, and it can be dereferenced through that index (for example, as Cmd(1)). An input parameter, which holds the value of the user-supplied percent royalty number. The input parameter is created as adParamInput for the input parameter type, and the data type is adInteger for integer. Because the data type of these stored procedure parameters is integer, there is no need to specify the data length as a parameter when defining them with the CreateParameter method.After each parameter is added to the Parameters collection, executing the query string creates a recordset. After the recordset is closed, the values for the return code and output parameters are available.
用select @@TRANCOUNT就可以知道当前运行到几重嵌套的位置
commit transaction
return(0)on_error:
rollback tran
return(1)
GO
于是可以根据return的返回值为 0 或 1 来判断存储过程是否执行成功?
2、事务中当然可以套事务,但容易引起混乱,慎用!!!!!!
Using Return Code and Output Parameters for Stored Procedures
Stored procedures can contain input parameters, output parameters, and return values. You specify input parameters, output parameters, and return values for a stored procedure through the Parameter object. In the case of output parameters and return values, the values are not returned until the data of the Recordset object has been fetched completely or the Recordset has been closed. The following stored procedure contains one input parameter, one output parameter, and a return parameter. The procedure selects those rows in the titles table of the pubs database where the royalty percent paid to the author is greater than the amount entered by the user (the input parameter). The program returns the number of rows as the output variable. If the program returns any rows, a return code of 0 is issued; if no rows are returned, a return code of 99 is issued.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
GOAn ADO code program that executes the stored procedure myProc is shown here.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 SubThe following parameters are needed for the myProc stored procedure: A return parameter to hold the return value (0 or 99).The return parameter is created as a return type of parameter adParamReturnValue, and the data type is adInteger for integer. Because the return parameter is the first parameter added to the collection, its index value is zero, and it can be dereferenced through that index (for example, as Cmd(0)).
An output parameter to hold the value of the count of the number of returned rows. The output parameter is created as adParamOuput for the output parameter type, and the data type is adInteger for integer. Because the output parameter is the second parameter added to the collection, its index value is 1, and it can be dereferenced through that index (for example, as Cmd(1)).
An input parameter, which holds the value of the user-supplied percent royalty number. The input parameter is created as adParamInput for the input parameter type, and the data type is adInteger for integer.
Because the data type of these stored procedure parameters is integer, there is no need to specify the data length as a parameter when defining them with the CreateParameter method.After each parameter is added to the Parameters collection, executing the query string creates a recordset. After the recordset is closed, the values for the return code and output parameters are available.