CODEWITHSUNDEEP
c#asp.netsql-serverreturn-value
SkyDawg33
SkyDawg33

Reputation: 41

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

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?

Upvotes: 4

Views: 2606

Answers (4)

James Michael Hare
James Michael Hare

Reputation: 38427

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)

Upvotes: 4

James Black
James Black

Reputation: 41838

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()
            }

Upvotes: 0

Harun
Harun

Reputation: 5179

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..

Upvotes: -1

Kirill
Kirill

Reputation: 3088

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

Upvotes: -1

Related Questions