none
Using the return value of stored procedures

    Question

  • Hi

     

    Will anyone here please tell me how can I use the return value of a stored procedure?

     

    I have a procedure which return a nonzero if succed, and -1 if fails. But I can't understand how can I access the value from visual basic.

     

    Need help!

     

    Kapalic

    Saturday, March 31, 2007 8:14 AM

Answers

  • You can either use the return value of the procedure or an output parameter. Here is a sample stored procedure that includes both:

     

    Code Snippet

     

    CREATE PROCEDURE spWithOutput

    @OutValue AS INT OUTPUT

    AS

    BEGIN

     

       -- The stored procedure code

     

       -- Set the output parameter value

       SET @OutValue = 0

     

       -- Return a certain value

       RETURN 1

     

    END

     

     

    The VB.NET code to retrieve both the output parameter value and the return value is:

     

    Code Snippet

     

    Function CallSP(ByVal strConn As String)

       'Open the connection to the database

       Dim connection As SqlConnection = New SqlConnection(strConn)

       connection.Open()

       Try

          'Set the command object to call the stored procedure

          Dim command As SqlCommand = New SqlCommand("spWithOutput", connection)

          command.CommandType = CommandType.StoredProcedure

     

          'Add the output parameter to the command parameters array

          command.Parameters.Add("@OutValue", "")

          command.Parameters(0).Direction = ParameterDirection.Output

     

          'Add the return value of the stored procedure to the command parameters array

          command.Parameters.Add("@ReturnValue", "")

          command.Parameters(1).Direction = ParameterDirection.ReturnValue

     

          'Execute the stored procedure

          command.ExecuteNonQuery()

     

          'Obtain the output parameter

          Dim OutValue As Integer

          OutValue = Integer.Parse(command.Parameters(0).Value)

     

          'Obtain the return value

          Dim RetValue As Integer

          RetValue = Integer.Parse(command.Parameters(1).Value)

     

       Catch ex As Exception

          Throw

       Finally

          connection.Close()

       End Try

    End Function

     

    I hope this answers your question.

     

    Best regards,

    Sami Samir

     

    Saturday, March 31, 2007 9:22 AM
  • Do you mean are you restricted with @ReturnValue as the name of the parameter? If this is the question then the answer is no. You can use any name of your choice. The only restriction is that it has to start with @.

     

    Code Snippet

     

    command.Parameters.Add("@ResponseCode", "")

     

    I hope this answers your question.

     

    Best regards,

    Sami Samir

     

     

    Monday, April 02, 2007 5:08 AM

All replies

  • You can either use the return value of the procedure or an output parameter. Here is a sample stored procedure that includes both:

     

    Code Snippet

     

    CREATE PROCEDURE spWithOutput

    @OutValue AS INT OUTPUT

    AS

    BEGIN

     

       -- The stored procedure code

     

       -- Set the output parameter value

       SET @OutValue = 0

     

       -- Return a certain value

       RETURN 1

     

    END

     

     

    The VB.NET code to retrieve both the output parameter value and the return value is:

     

    Code Snippet

     

    Function CallSP(ByVal strConn As String)

       'Open the connection to the database

       Dim connection As SqlConnection = New SqlConnection(strConn)

       connection.Open()

       Try

          'Set the command object to call the stored procedure

          Dim command As SqlCommand = New SqlCommand("spWithOutput", connection)

          command.CommandType = CommandType.StoredProcedure

     

          'Add the output parameter to the command parameters array

          command.Parameters.Add("@OutValue", "")

          command.Parameters(0).Direction = ParameterDirection.Output

     

          'Add the return value of the stored procedure to the command parameters array

          command.Parameters.Add("@ReturnValue", "")

          command.Parameters(1).Direction = ParameterDirection.ReturnValue

     

          'Execute the stored procedure

          command.ExecuteNonQuery()

     

          'Obtain the output parameter

          Dim OutValue As Integer

          OutValue = Integer.Parse(command.Parameters(0).Value)

     

          'Obtain the return value

          Dim RetValue As Integer

          RetValue = Integer.Parse(command.Parameters(1).Value)

     

       Catch ex As Exception

          Throw

       Finally

          connection.Close()

       End Try

    End Function

     

    I hope this answers your question.

     

    Best regards,

    Sami Samir

     

    Saturday, March 31, 2007 9:22 AM
  • Hi Sami,

     

    Once again, you are with a great help! Thank you so much. People in another forum was telling me it is not possible without adding a parameter to access the return value of a sproc. But at last I get the solution.

     

    Wishing you all the best!

     

    Kapalic

    Sunday, April 01, 2007 3:26 AM
  • Hi Sami,

     

     Sami Samir wrote:
     

          'Add the return value of the stored procedure to the command parameters array

          command.Parameters.Add("@ReturnValue", "")

          command.Parameters(1).Direction = ParameterDirection.ReturnValue

     

    Can I use anything in the name of the parameter which returns value?

     

    Regards

    Kapalic

    Monday, April 02, 2007 3:20 AM
  • Do you mean are you restricted with @ReturnValue as the name of the parameter? If this is the question then the answer is no. You can use any name of your choice. The only restriction is that it has to start with @.

     

    Code Snippet

     

    command.Parameters.Add("@ResponseCode", "")

     

    I hope this answers your question.

     

    Best regards,

    Sami Samir

     

     

    Monday, April 02, 2007 5:08 AM