CODEWITHSUNDEEP
c#.netsql-serverstored-procedures
Vaibhav Jain
Vaibhav Jain

Reputation: 34447

Fetch return value from a stored procedure

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

Upvotes: 1

Views: 1063

Answers (4)

Manish Parakhiya
Manish Parakhiya

Reputation: 3798

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

Upvotes: -1

Pranay Rana
Pranay Rana

Reputation: 176956

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

Upvotes: 2

Shivkant
Shivkant

Reputation: 4619

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#

Upvotes: 1

Suhas
Suhas

Reputation: 8478

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.

Upvotes: 0

Related Questions