Fetch return value from a stored procedure

Question

How to fetch the return value from a stored procedure?

enter image description here

I noticed that the stored procedure returns an integer on its own. I need to fetch it in C#.

Well you need to tell how you are calling the SP, some code would be helpful — V4Vendetta, Aug 01 '12 at 06:30
dont forget to upvote and mark it as accepted if it works for you — Pranay Rana, Aug 01 '12 at 09:12

score 2 · Accepted Answer · edited Aug 01 '12 at 07:16

You can make use of Return parameter in C# to get that value. Like as below

SqlParameter retval = sqlcomm.Parameters.Add("@return_value", SqlDbType.VarChar);
retval.Direction = ParameterDirection.ReturnValue;
sqlcomm.ExecuteNonQuery(); 
string retunvalue = (string)sqlcomm.Parameters["@return_value"].Value;

Note your procedure must return a value to be able to fetch it:

create procedure [dbo].[usp_GetNewSeqVal]
       @SeqName nvarchar(255)
 as begin 
    declare @NewSeqVal int
    select @NewSeqVal  =1
   ---other statement
    return @NewSeqVal
 end

score 1 · Answer 2 · edited May 23 '17 at 10:30

1

Check Following Code:

SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
retval.Direction = ParameterDirection.ReturnValue;
sqlcomm.ExecuteNonQuery(); // MISSING
string retunvalue = (string)sqlcomm.Parameters["@b"].Value;

For further reference check link: Getting return value from stored procedure in C#

edited May 23 '17 at 10:30

Community

1
1

answered Aug 01 '12 at 06:31

Shivkant

4,509
1
19
16

score 0 · Answer 3 · answered Aug 01 '12 at 06:31

In your SP, you need to return KategoriId. By default SP returns the number of rows affected by the latest insert, update or delete statement.

And mke sure you use proper data type in C# and in database column KategoriId to make this work. I had problems in past when database column was Decimal and I tried to assign the return value to an int and it never worked.

score -1 · Answer 4 · edited Aug 01 '12 at 12:26

-1

You can use output parameter in store procedure or use ExecuteScalar instead of ExecuteNonQuery.

edited Aug 01 '12 at 12:26

Pranay Rana

175,020
35
237
263

answered Aug 01 '12 at 06:33

Manish Parakhiya

3,732
3
22
25

This is just all wrong: `ExecuteScalar` returns a single row, single column that's being **SELECTED** in the stored procedure - it does **NOT** return the value from the `RETURN x` statement! The `OUTPUT` parameter also has **nothing to do** with the value from the `RETURN` statement. – marc_s Aug 01 '12 at 07:17

Fetch return value from a stored procedure

4 Answers4