Execute SQL Server Stored Procedure: Output Parameter

[bernie321]

Hi

I have the below code for executing stored procedures and retreviing the returned value.

It works perfectly to execute procedures with no parameters, but as soon as I add parameters it errors with:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

Any help would be much appreciated, I have tried many types of example code from google with no luck.

Thanks
B

'Calling Code
MsgBox(SQLDataProvider_SP.execute_SP_ReturnParam(strConnectionString, "dbo.sp_mytestprocedure", "DBNAME"))

'Function
Public Shared Function execute_SP_ReturnParam(ByVal strConString As String, ByVal strSPName As String, ByVal strDBName As String)

Dim objCN As SqlConnection = New SqlConnection(strConString)

Dim objCommand As SqlCommand = New SqlCommand(strSPName, objCN)
objCommand.CommandTimeout = 20

objCommand.CommandType = CommandType.StoredProcedure


Dim inputValue As New SqlParameter("@iReturn", SqlDbType.Int)
inputValue.Direction = ParameterDirection.Output
objCommand.Parameters.Add(inputValue)

objCN.Open()

Dim myReader As SqlDataReader = objCommand.ExecuteReader()
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()

objCN.Close()
End Function

 

[bboffin]

You have assigned the wrong 'direction' to the @Return parameter. It should be ParameterDirection.ReturnValue which is specifically reserved for this purpose. Also @Return must not be declared in the Stored Procedure. It will be populated automatically from the Return value.

You may find like to try using SqlCommandBuilder.DervicePaarmeters which always generates a ReturnValue as the first parameter.



Bob Boffin

 

[bernie321]

Hi Bob,

Thanks for your post, I have made the change below, but how would you suggest the changes need to be made regarding not declaring @iReturn?

inputValue.Direction = ParameterDirection.ReturnValue

Also i thought i better post the SP, in case it is of use:

CREATE procedure dbo.sp_mytestprocedure
@iReturn int
as
begin transaction
select @iReturn = TestField from TestTable
commit
return @iReturn + 1
GO

 

[bboffin]

First, you don't need to use a transaction for a SELECT statement as it makes no DB changes so there's nothing to commit or rollback.

Try:

CREATE procedure dbo.sp_mytestprocedure
as
declare @iReturn int
    select @iReturn = TestField from TestTable
    return @iReturn + 1
GO

OR if you wish to make @iReturn an output parameter as you originally tried:

CREATE procedure dbo.sp_mytestprocedure
    @iReturn int OUTPUT
as
    select @iReturn = TestField from TestTable
    SET @iReturn = @iReturn + 1
GO

Not that you MUST declare @iReturn as an OUTPUT parameter within the SP as well as in the call to the SP. I know it seems superfluous but that's the way it is.




Bob Boffin

 

[bernie321]

Thanks Bob,

The SP was only an example (although apart from the select statement & the name it is the same), I removed most of the code as it is part of some Purchased software so thought I should not post the actual code.

Sadly I daren’t change the SP for fear that it may cause some problems with their software down the lines.

Is there any way of working with it as it is?

Thanks
B

 

[bboffin]

It may not be possible if @iReturn is declared as a parameter in the SP without the OUTPUT.

You could try leaving it as an input parameter and simply passing in a value of zero.

Something like this:

    Public Shared Function execute_SP_ReturnParam(ByVal strConString As String, ByVal strSPName As String, ByVal strDBName As String)

        Dim objCN As SqlConnection = New SqlConnection(strConString)

        Dim objCommand As SqlCommand = New SqlCommand(strSPName, objCN)
        objCommand.CommandTimeout = 20

        objCommand.CommandType = CommandType.StoredProcedure

        objCN.Open()
        ' derive parameters of the SP
        ' this will create the first parameter as a ReturnValue
        SqlCommandBuilder.DeriveParameters(objCommand)
        ' set the value of the @iReturn parameter to 0
        objCommand.Parameters("@iReturn").value = 0
        ' execute the SP
        Dim myReader As SqlDataReader = objCommand.ExecuteReader()
        ' show the value returned in the first parameter
        Console.WriteLine("Return Value = {0}", objCommand.Parameters(0).value)
.
.
.
.

        myReader.Close()

        objCN.Close()
    End Function

Bob Boffin

 

[bernie321]

Sorry for the delay in replying.

Thanks this worked perfectly

B