none
Return Value from a stored procedure

    Question

  • Hi,

    From a vb.net  program I have the following snippet of code

    rslt = 0

    dbConnection = New SqlConnection(gsSQLConnectionString)

    dbConnection.Open()

    sqlcomm = New SqlCommand("usp_tblReviewerTypeDelete", dbConnection)

    sqlcomm.CommandType = CommandType.StoredProcedure

    sqlcomm.Parameters.Add(New SqlParameter("@ReviewerTypeId", SqlDbType.Int)).Value = RvwrTypeId

    rslt = sqlcomm.ExecuteNonQuery()

    MessageBox.Show("Rslt is " & rslt, "Reviewer Type - Delete", MsgBoxStyle.OKOnly, MessageBoxIcon.Information)

    If rslt = 0 Then

    MessageBox.Show("Reviewer type " & strRvwrTypSlctd & " deleted", "Reviewer Type - Delete", MsgBoxStyle.OKOnly, MessageBoxIcon.Information)

    dbConnection.Close()

    FillListBox()

    Else

    MessageBox.Show("Reviewer type " & strRvwrTypSlctd & " not deleted", "Reviewer Type - Delete", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

    dbConnection.Close()

    End If

    The store procedure looks

    /*
    Name: usp_tblReviewerTypeDelete
    Desc: Deletes a new Reviewer Type record
    Auther: Denis Kahl
    Created 4/1/06
    */
    CREATE PROCEDURE dbo.usp_tblReviewerTypeDelete
    (
    @ReviewerTypeId int
    )

    AS

    DECLARE @myERROR int, @myRowCount int

    SET NOCOUNT ON

    BEGIN TRAN

    DELETE FROM tblReviewerTypes
     WHERE ReviewerTypeId = @ReviewerTypeId

    SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT

    IF @myERROR = 0
     BEGIN
     COMMIT TRAN
     RETURN 0
     END
    ELSE
     BEGIN
     ROLLBACK TRAN
     RETURN @myERROR
     END
    GO

    @myError gets set to 0(zero) so I am expecting to get a return of 0 (which I've deemed to mean success). However I am getting a return code of -1 and yes the record is deleted.

    I have also tried setting the return code to @myError and to @myRowCount and I still get -1.

    Can anybody see where I have gone wrong? I have coded an Insert stored procedure in a similar manner and it returns 0 so I am a little puzzled.

    TIA

    Denis

     

     

     

    Thursday, January 05, 2006 8:04 AM

Answers

  • Hi denis,

    The problem you've got here (and it's an easy mistake to make) is that the ExecuteNonQuery() method doesn't return the return value of the stored procedure. The return value of this method is something different altogether - the number of rows affected by the call.

    To get the return value of the stored procedure, you need to add another parameter to to your command object.

    Dim oRetParam as new SqlParameter("@ret", SqlDbType.Int)

    oRetParam.Direction = ParameterDirection.ReturnValue

    You then add this parameter to the parameters collection _before_ (well, i always do it first, not sure why) adding your other parameters.

    Once you've then called ExecuteNonQuery(), you can then see the return value by accessing oRetParam.Value().

    HTH,

    --Geoff

    Thursday, January 05, 2006 11:23 PM

All replies

  • Hi denis,

    The problem you've got here (and it's an easy mistake to make) is that the ExecuteNonQuery() method doesn't return the return value of the stored procedure. The return value of this method is something different altogether - the number of rows affected by the call.

    To get the return value of the stored procedure, you need to add another parameter to to your command object.

    Dim oRetParam as new SqlParameter("@ret", SqlDbType.Int)

    oRetParam.Direction = ParameterDirection.ReturnValue

    You then add this parameter to the parameters collection _before_ (well, i always do it first, not sure why) adding your other parameters.

    Once you've then called ExecuteNonQuery(), you can then see the return value by accessing oRetParam.Value().

    HTH,

    --Geoff

    Thursday, January 05, 2006 11:23 PM
  • Geoff,

    Many thanks for your response. It did occur to me later (around midnight - too late) that perhaps I should do a return value but I was a bit hung up on the apparent success of an Insert stored procedure with the same structure which I will now revisit.

    I have ended up with an Ouput parameter of which my previous attempts to use had failed but have now succeeded.

    So all round a successful result.

    Cheers

    Denis

     

    Friday, January 06, 2006 10:51 AM