Return Value from Stored Procedure not set

Question

I have the following very basic stored procedure:

CREATE PROCEDURE [dbo].[GetNumberToProcess]

AS

RETURN 999

I then have some code using Enterprise Library to run and get the return value:

        Dim cmd As DbCommand
        Dim ResultValue as String
        Dim lDBCommand as String = "dbo.GetNumberToProcess"
        Dim actionDB As Sql.SqlDatabase = New Sql.SqlDatabase(lConnectionString)
        cmd = actionDB.GetSqlStringCommand(lDBCommand)

        Dim SQLreturnValue As New SqlParameter("RETURN_VALUE", DbType.Int32)
        SQLreturnValue.Direction = ParameterDirection.ReturnValue

        cmd.Parameters.Add(SQLreturnValue)

        ' Execute the command and put the result into the ResultValue for later processing
        actionDB.ExecuteNonQuery(cmd).ToString()
        ResultValue = cmd.Parameters("RETURN_VALUE").Value.ToString

Problem is that all I ever get back as ResultValue is "0" when I should get "999" (the Stored Proc is very cut down just so that I can get to the bottom of why it's not working).

According to the multiple examples I've seen on the web this should work.

Anyone got any suggestions as to why it doesn't?

Answer 1

have your tried

ResultValue = SQLreturnValue.Value.ToString()

I think that's just a syntax thing tho, shouldn't make a diff.

I personally do not have a name for my return param and it works fine:

var returnCode = new SqlParameter();
returnCode.Direction = System.Data.ParameterDirection.ReturnValue;
returnCode.DbType = System.Data.DbType.Int32;

Maybe the name RETURN_VALUE is messing with it?

Answer 2

Change your procedure to be REALLY simple:

ALTER PROCEDURE [dbo].[GetNumberToProcess] AS RETURN 999

Is ResultValue still 0 after this change?

Answer 3

Your stored proc is obviously fine, I don't use the EntLib much I think your problem is the line

cmd = actionDB.GetSqlStringCommand(lDBCommand)

Try using this instead

cmd = actionDB.GetStoredProcCommand(lDBCommand)

Answer 4

If you are going to get back an output parameter instead of a resultset you need to declare an output parameter in your sproc and assign it a value.

CREATE PROCEDURE [dbo].[GetNumberToProcess] ( @rtInt as int OUT ) AS

select @rtInt = 999

---

Dim SQLreturnValue As New SqlParameter("@rtInt", DbType.Int32)

ResultValue = cmd.Parameters("@rtInt").Value.ToString

Answer 5

My guess is that your code isn't handling the result very well. Short answer: try changing your stored procedure to:

CREATE PROCEDURE [dbo].[GetNumberToProcess]

AS

SET NOCOUNT ON
RETURN 999

GO

The NOCOUNT setting will prvent SQL from sending the '1 record(s) affected' which could influence your return value.