Retrieving SQL SP Output Parameters

  • Thread starter Thread starter bh
  • Start date Start date
B

bh

I have a stored procedure in SQL 2000 that inserts a row & returns an output parameter variable for the identity of the inserted row, as well as another output parameter.

I also have an method in an ado.net class (which resides in a separate assembly & is referenced by the application) that returns the return value code to the calling asp.net page, which in turn tests for a result of 0 for successful insertion. If the ado code was within the asp.net page, itself, I might have tried a session variable, but this is not allowed, since the method is in a self-contained class.

The question is, how would I also return the outputted "iInsertedRowID" and "dCalculatedValue" variables back to the calling procedure for use, after testing and finding the successful result code? Thanks in advance for help offered.

bh

Public Shared Function InsertProcedure(ByVal InputStuff As String) As Integer
Dim cn As SqlConnection = Connection()
Dim cmd As New SqlCommand("procInsert", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@ReturnValue", SqlDbType.Int)
cmd.Parameters("@ReturnValue").Direction = ParameterDirection.ReturnValue
cmd.Parameters.AddWithValue("@InputStuff ", InputStuff )
cmd.Parameters.Add("@InsertedRowID", SqlDbType.Int)
cmd.Parameters("@InsertedRowID").Direction = ParameterDirection.Output
cmd.Parameters.Add("@InsertedRowID", SqlDbType.Int)
cmd.Parameters("@InsertedRowID").Direction = ParameterDirection.Output
cmd.Parameters.Add("@CalculatedValue").SqlDbType.Decimal)
cmd.Parameters("@CalculatedValue").Direction = ParameterDirection.Output
cn.Open()
cmd.ExecuteNonQuery()
Dim iInsertedRowID as integer = cmd.Parameters("@InsertedRowID").Value
Dim dCalculatedValue as decimal = cmd.Parameters("@CalculatedValue").Value
cn.Close()

Return Convert.ToInt32(cmd.Parameters("@ReturnValue").Value)

End Function
 
Thank you for the help. It worked like a charm. And yes--I'm a pretty
retro-gal...still living in the stone age :)


You have to make a mini wrapper object and send it back.

Or use "by ref" variables.

I prefer the mini wrapper object.


public class InsertResult


private _iInsertedRowID as int32 = 0;
private _dCalculatedValue as decimal = 0;
private _returnValue as int32= 0;

''''//encapsulate the 3 variables above as properties........

end class


Public Shared Function InsertProcedure(ByVal InputStuff As String) As
InsertResult

dim returnObject as InsertResult

returnObject.InsertedRowID = 1 ' use your values as you have
below
returnObject.CalculatedValue= 1 ' use your values as you have below
returnObject.ReturnValue = 1 ' use your values as you have below

return returnObject


end function




By the way...hungarian notation is WAY OUT these days.
Note that video is from the 60's, which I think it when hungarian notation
was invented.





I have a stored procedure in SQL 2000 that inserts a row & returns an
output parameter variable for the identity of the inserted row, as well as
another output parameter.

I also have an method in an ado.net class (which resides in a separate
assembly & is referenced by the application) that returns the return value
code to the calling asp.net page, which in turn tests for a result of 0 for
successful insertion. If the ado code was within the asp.net page, itself,
I might have tried a session variable, but this is not allowed, since the
method is in a self-contained class.

The question is, how would I also return the outputted "iInsertedRowID"
and "dCalculatedValue" variables back to the calling procedure for use,
after testing and finding the successful result code? Thanks in advance for
help offered.

bh

Public Shared Function InsertProcedure(ByVal InputStuff As String) As
Integer
Dim cn As SqlConnection = Connection()
Dim cmd As New SqlCommand("procInsert", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@ReturnValue", SqlDbType.Int)
cmd.Parameters("@ReturnValue").Direction =
ParameterDirection.ReturnValue
cmd.Parameters.AddWithValue("@InputStuff ", InputStuff )
cmd.Parameters.Add("@InsertedRowID", SqlDbType.Int)
cmd.Parameters("@InsertedRowID").Direction = ParameterDirection.Output
cmd.Parameters.Add("@InsertedRowID", SqlDbType.Int)
cmd.Parameters("@InsertedRowID").Direction = ParameterDirection.Output
cmd.Parameters.Add("@CalculatedValue").SqlDbType.Decimal)
cmd.Parameters("@CalculatedValue").Direction =
ParameterDirection.Output
cn.Open()
cmd.ExecuteNonQuery()
Dim iInsertedRowID as integer = cmd.Parameters("@InsertedRowID").Value
Dim dCalculatedValue as decimal =
cmd.Parameters("@CalculatedValue").Value
cn.Close()

Return Convert.ToInt32(cmd.Parameters("@ReturnValue").Value)

End Function
 
PS

Your
cn.Close()

should probably be in a finally block

Dim cn As SqlConnection = Nothing

Try

cn = Connection()
Dim cmd As New SqlCommand("procInsert", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@ReturnValue", SqlDbType.Int)
cmd.Parameters("@ReturnValue").Direction =
ParameterDirection.ReturnValue
cmd.Parameters.AddWithValue("@InputStuff ", InputStuff )
cmd.Parameters.Add("@InsertedRowID", SqlDbType.Int)
cmd.Parameters("@InsertedRowID").Direction = ParameterDirection.Output
cmd.Parameters.Add("@InsertedRowID", SqlDbType.Int)
cmd.Parameters("@InsertedRowID").Direction = ParameterDirection.Output
cmd.Parameters.Add("@CalculatedValue").SqlDbType.Decimal)
cmd.Parameters("@CalculatedValue").Direction =
ParameterDirection.Output
cn.Open()
cmd.ExecuteNonQuery()
Dim iInsertedRowID as integer = cmd.Parameters("@InsertedRowID").Value
Dim dCalculatedValue as decimal =
cmd.Parameters("@CalculatedValue").Value

Finally
if (not ( cn is nothing )) then
cn.Close()
end if

End Try



Notice there is no Catch.
But the cn.Close will execute even if there is an exception.

http://blogs.msdn.com/brada/archive/2004/12/03/274718.aspx
 
Back
Top