How can I get the return value from my stored procedure?

Question

Here's my SQL:

IF (SELECT Status FROM dbo.Coupon WHERE Guid = @pGuid) = 0
BEGIN
    UPDATE
        dbo.Coupon

    SET
        Status = @pStatus   

    WHERE
        Guid = @pGuid

    RETURN 0    
END

ELSE

RETURN 1;

And here's my C#:

try
            {
                DbCommand command = db.GetStoredProcCommand("upd_Coupon_p");
                db.AddInParameter(command, "@pGuid", DbType.String, s);
                db.AddInParameter(command, "@pStatus", DbType.Byte, 1);
                ds = db.ExecuteDataSet(command);
             }

How can I get the return value of 0 or 1 inside of my code?

is db an instance of a custom database class? Oh wait! You're using the EL! — James Michael Hare, Jul 12 '11 at 14:47

James Michael Hare · Answer 1 · 2011-07-12T14:54:40.707

5

You add a return value parameter, like this:

For SqlCommand:

parameters.Add("@retValue", DbType.Int32, ParameterDirection.ReturnValue);

For the EL, you'd want to use db.AddParameter() and specify ParameterDirection.ReturnValue.

In addition, as long as row count is on in your database, for the update you are performing you could use the result from ExecuteNonQuery() that tells you how many rows were affected on an update/insert/delete/etc. That way you could handle if rows affected was 0 (couldn't find any)

edited Jul 12 '11 at 14:54

answered Jul 12 '11 at 14:46

James Michael Hare

37,767
9
73
83

The answer here fleshes it out a bit more: http://stackoverflow.com/questions/520735/can-you-use-cmd-executescalar-when-the-sproc-uses-return-value – Adam Houldsworth Jul 12 '11 at 14:49

score 0 · Answer 2 · answered Jul 12 '11 at 14:50

This is what I did, so basically just use ReturnValue, but the other parts may be useful.

            var retparam = new SqlParameter("@return", System.Data.SqlDbType.Int) { Direction = System.Data.ParameterDirection.ReturnValue };
            comm.Parameters.Add(retparam);
            comm.ExecuteNonQuery();
            int ret = 0;
            if (retparam == null)
            {
                System.Diagnostics.Debug.WriteLine("retparam was null");
            }
            else if (retparam.Value == null)
            {
            }
            else
            {
               // use reparam.Value.ToString()
            }

score -1 · Answer 3 · answered Jul 12 '11 at 14:48

-1

What is DbCommand.ExecuteDataSet() and why don't you use ExecuteScalar()?

answered Jul 12 '11 at 14:48

Kirill

3,028
1
17
18

score -1 · Answer 4 · answered Jul 12 '11 at 14:48

-1

Declare a variable as output and get it inside the call function of Data Access section.

see the code below,

In the stored procedure,

  @ReturnStatus int output //inside your stored procedure argument section

In the Data Access section use the following,

   AddOutParameter(.....);

Hope this helps..

answered Jul 12 '11 at 14:48

Harun

5,109
4
38
60

There's no need to add an output parameter if all you're interested in is the return value - that's accessible as detailed in the other answers using the returnValue enum. Admittedly there's nothing wrong with what you've suggested, it's just a bit unnecessary. – Simon Halsey Jul 12 '11 at 15:17

How can I get the return value from my stored procedure?

4 Answers4