Fetch return value from a stored procedure

How to fetch the return value from a stored procedure?

enter image description here

  • Explain group by query
  • Selecting a user-defined scalar function that takes as a parameter another field
  • Conversion failed when converting the nvarchar value to data type int
  • How to combine the result of SQL server table rows using bitwise operator?
  • sql - query which extracts all records but only the most recent record of a duplicate
  • How to return bool from stored proc
  • I noticed that the stored procedure returns an integer on its own. I need to fetch it in C#.

  • SQL Server 2008: moving of data between environments
  • Tools for Building an OCA (Occasionally Connected Application)
  • Why do I get “Database already exists” when I'm using “AttachDbFileName” when I move the solution to another directory?
  • A checklist for fixing .NET applications to SQL Server timeout problems and improve execution time
  • SQL Job Status in Winforms
  • SQL Azure BACPAC extraction
  • 4 Solutions collect form web for “Fetch return value from a stored procedure”

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

    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#

    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.

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.