How to fetch the return value from a stored procedure?
I noticed that the stored procedure returns an integer on its own. I need to fetch it in C#.
-
May I ask what you are trying to achieve here?Umesh– Umesh2012-08-01 06:30:26 +00:00Commented Aug 1, 2012 at 6:30
-
Well you need to tell how you are calling the SP, some code would be helpfulV4Vendetta– V4Vendetta2012-08-01 06:30:41 +00:00Commented Aug 1, 2012 at 6:30
-
dont forget to upvote and mark it as accepted if it works for youPranay Rana– Pranay Rana2012-08-01 09:12:31 +00:00Commented Aug 1, 2012 at 9:12
Add a comment
|
4 Answers
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
Comments
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#
Comments
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.
Comments
You can use output parameter in store procedure or use ExecuteScalar instead of ExecuteNonQuery.
1 Comment
marc_s
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.