Getting return value from stored procedure in C

Question

I have the following query   set ANSI NULLS ON set QUOTED IDENTIFIER ON go  ALTER PROCEDURE  dbo   Validate   a varchar 50    b varchar 50  output  AS  SET  Password     SELECT Password FROM dbo tblUser WHERE Login    a   RETURN  b GO   This compiles perfectly fine  I want to execute this query and get the return value  My code is below     SqlConnection SqlConn   new SqlConnection System Configuration ConfigurationManager ConnectionStrings  MyLocalSQLServer   ConnectionString ToString             System Data SqlClient SqlCommand sqlcomm   new System Data SqlClient SqlCommand  Validate   SqlConn            string returnValue   string Empty           try                       SqlConn Open                sqlcomm CommandType   CommandType StoredProcedure               SqlParameter param   new SqlParameter   a   SqlDbType VarChar               param Direction   ParameterDirection Input              param Value   Username              sqlcomm Parameters Add param                  SqlParameter retval   sqlcomm Parameters Add   b   SqlDbType VarChar               retval Direction   ParameterDirection ReturnValue                string retunvalue    string sqlcomm Parameters   b   Value    Note  Exception handling cut to keep the code short  Everytime I get to the last line  null is returned  What s the logic error with this code

User · Answer

When we return a value from Stored procedure without select statement. We need to use "ParameterDirection.ReturnValue" and "ExecuteScalar" command to get the value.

CREATE PROCEDURE IsEmailExists
    @Email NVARCHAR(20)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF EXISTS(SELECT Email FROM Users where Email = @Email)
    BEGIN
        RETURN 0 
    END
    ELSE
    BEGIN
        RETURN 1
    END
END

in C#

GetOutputParaByCommand("IsEmailExists")

public int GetOutputParaByCommand(string Command)
        {
            object identity = 0;
            try
            {
                mobj_SqlCommand.CommandText = Command;
                SqlParameter SQP = new SqlParameter("returnVal", SqlDbType.Int);
                SQP.Direction = ParameterDirection.ReturnValue;
                mobj_SqlCommand.Parameters.Add(SQP);
                mobj_SqlCommand.Connection = mobj_SqlConnection;
                mobj_SqlCommand.ExecuteScalar();
                identity = Convert.ToInt32(SQP.Value);
                CloseConnection();
            }
            catch (Exception ex)
            {

                CloseConnection();
            }
            return Convert.ToInt32(identity);
        }

We get the returned value of SP "IsEmailExists" using above c# function.

User · Answer

This Line of code returns Store StoredProcedure returned value from SQL Server   cmd Parameters Add   id   System Data SqlDbType Int  Direction   System Data ParameterDirection ReturnValue                  cmd ExecuteNonQuery      Atfer Execution of query value will returned from SP  id    int cmd Parameters   id   Value

User · Answer

Suppose you need to pass Username and Password to Stored Procedure and know whether login is successful or not and check if any error has occurred in Stored Procedure   public bool IsLoginSuccess string userName  string password        try               SqlConnection SQLCon   new SqlConnection WebConfigurationManager ConnectionStrings  SqlConnector   ConnectionString           SqlCommand sqlcomm   new SqlCommand            SQLCon Open            sqlcomm CommandType   CommandType StoredProcedure          sqlcomm CommandText    spLoginCheck      Stored Procedure name         sqlcomm Parameters AddWithValue   Username   userName      Input parameters         sqlcomm Parameters AddWithValue   Password   password      Input parameters             Your output parameter in Stored Procedure                    var returnParam1   new SqlParameter                       ParameterName     LoginStatus               Direction   ParameterDirection Output              Size   1                                        sqlcomm Parameters Add returnParam1               Your output parameter in Stored Procedure           var returnParam2   new SqlParameter                       ParameterName     Error               Direction   ParameterDirection Output              Size   1000                                         sqlcomm Parameters Add returnParam2            sqlcomm ExecuteNonQuery             string error    string sqlcomm Parameters   Error   Value          string retunvalue    string sqlcomm Parameters   LoginStatus   Value                                catch  Exception ex                   return false      Your connection string in Web Config   lt connectionStrings gt       lt add name  SqlConnector           connectionString  data source   SQLEXPRESS Integrated Security SSPI Initial Catalog Databasename User id yourusername Password yourpassword           providerName  System Data SqlClient    gt     lt  connectionStrings gt    And here is the Stored Procedure for reference  CREATE PROCEDURE spLoginCheck      Username Varchar 100        Password Varchar 100         LoginStatus char 1    null output       Error Varchar 1000  output  AS BEGIN      SET NOCOUNT ON      BEGIN TRY         BEGIN              SET  Error    None              SET  LoginStatus                   IF EXISTS SELECT TOP 1   FROM EMP MASTER WHERE EMPNAME  Username AND EMPPASSWORD  Password              BEGIN                 SET  LoginStatus  Y              END              ELSE             BEGIN                 SET  LoginStatus  N              END          END     END TRY      BEGIN CATCH         BEGIN                        SET  Error   ERROR MESSAGE           END     END CATCH END GO

User · Answer

retval Direction   ParameterDirection Output    ParameterDirection ReturnValue should be used for the  return value  of the procedure  not output parameters  It gets the value returned by the SQL RETURN statement  with the parameter named  RETURN VALUE    Instead of RETURN  b you should SET  b   something  By the way  return value parameter is always int  not string

User · Answer

There are two things to fix about this   First set up the stored procedure to store the value in the output   not return   parameter   set ANSI NULLS ON set QUOTED IDENTIFIER ON go  ALTER PROCEDURE  dbo   Validate   a varchar 50    b varchar 50  output  AS  SET  b     SELECT Password FROM dbo tblUser WHERE Login    a   RETURN GO   This will but the password into  b and you will get it as a return parameter   Then to get it in your C  do this   SqlConnection SqlConn   new SqlConnection System Configuration ConfigurationManager ConnectionStrings  MyLocalSQLServer   ConnectionString ToString         System Data SqlClient SqlCommand sqlcomm   new System Data SqlClient SqlCommand  Validate   SqlConn        string returnValue   string Empty       try               SqlConn Open            sqlcomm CommandType   CommandType StoredProcedure           SqlParameter param   new SqlParameter   a   SqlDbType VarChar  50           param Direction   ParameterDirection Input          param Value   Username          sqlcomm Parameters Add param              SqlParameter retval   new SqlParameter   b   SqlDbType VarChar  50           retval Direction   ParameterDirection ReturnValue          sqlcomm Parameters Add retval            sqlcomm ExecuteNonQuery            SqlConn Close             string retunvalue   retval Value ToString

User · Answer

This is building on Joel s and Mehrdad s answers  you re never binding the parameter of the retval to the sqlcommand  You need a  sqlcomm Parameters Add retval     and to make sure you re running the command  sqlcomm ExecuteNonQuery      I m also not sure why you have 2 return value strings  returnValue and retunvalue

User · Answer

The value you are trying to get is not a return value but an output parameter  You need to change parametere direction to Output  SqlParameter retval   sqlcomm Parameters Add  quot  b quot   SqlDbType VarChar   retval Direction   ParameterDirection Output  command ExecuteNonquery    string retunvalue    string sqlcomm Parameters  quot  b quot   Value

User · Answer

For  net core 3 0 and dapper  If your stored procedure returns this  select ID  FILE NAME from dbo FileStorage where ID    select max ID  from dbo FileStorage    Then in c    var data     dbConnection Query lt FileUploadQueryResponse gt                  quot dbo insertFile quot   whateverParameters  commandType  CommandType StoredProcedure   ToList     var storedFileName   data 0  FILE NAME   var id   data 0  ID   As you can see  you can define a simple class to help with retrieving the actual values from dapper s default return structure  which I found impossible to work with   public class FileUploadQueryResponse         public string ID   get  set        public string FILE NAME   get  set

User · Answer

When you use   cmd Parameters Add   RETURN VALUE   SqlDbType Int  Direction   ParameterDirection ReturnValue    you must then ensure your stored procedure has   return  RETURN VALUE    at the end of the stored procedure

User · Answer

There are multiple problems here    It is not possible  You are trying to return a varchar  Stored procedure return values can only be integer expressions  See official RETURN documentation  https   msdn microsoft com en-us library ms174998 aspx  Your sqlcomm was never executed  You have to call sqlcomm ExecuteNonQuery    in order to execute your command      Here is a solution using OUTPUT parameters  This was tested with     Windows Server 2012   NET v4 0 30319  C  4 0    SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO  ALTER PROCEDURE  dbo   Validate       a varchar 50        b varchar 50  OUTPUT AS BEGIN     DECLARE  b AS varchar 50     SELECT Password FROM dbo tblUser WHERE Login    a      SELECT  b  END   SqlConnection SqlConn       var sqlcomm   new SqlCommand  Validate   SqlConn    string returnValue   string Empty   try       SqlConn Open        sqlcomm CommandType   CommandType StoredProcedure       SqlParameter param   new SqlParameter   a   SqlDbType VarChar       param Direction   ParameterDirection Input      param Value   Username      sqlcomm Parameters Add param        SqlParameter output   sqlcomm Parameters Add   b   SqlDbType VarChar       ouput Direction   ParameterDirection Output       sqlcomm ExecuteNonQuery       This line was missing      returnValue   output Value ToString                the rest of code    catch  SqlException ex        throw ex

User · Answer

I was having tons of trouble with the return value  so I ended up just selecting stuff at the end   The solution was just to select the result at the end and return the query result in your functinon   In my case I was doing an exists check   IF  EXISTS  SELECT RoleName FROM dbo Roles WHERE  RoleName   RoleName        SELECT 1 ELSE     SELECT 0   Then   using  SqlConnection cnn   new SqlConnection ConnectionString         SqlCommand cmd   cnn CreateCommand        cmd CommandType   CommandType StoredProcedure      cmd CommandText    RoleExists       return  int  cmd ExecuteScalar       You should be able to do the same thing with a string value instead of an int

User · Answer

This SP looks very strange  It does not modify what is passed to  b  And nowhere in the SP you assign anything to  b  And  Password is not defined  so this SP will not work at all   I would guess you actually want to return  Password  or to have SET  b    SELECT      Much simpler will be if you modify your SP to  note  no OUTPUT parameter    set ANSI NULLS ON set QUOTED IDENTIFIER ON go  ALTER PROCEDURE  dbo   Validate   a varchar 50   AS  SELECT TOP 1 Password FROM dbo tblUser WHERE Login    a   Then  your code can use cmd ExecuteScalar  and receive the result

User · Answer

May be this will help   Database script   USE  edata  GO  SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO   CREATE PROCEDURE  dbo   InsertNewUser     neuname NVARCHAR 255     neupassword NVARCHAR 255     neuposition NVARCHAR 255      AS  BEGIN   BEGIN TRY   DECLARE  check INT    SET  check    SELECT count eid  FROM eusers WHERE euname    neuname    IF  check   0   INSERT INTO  eusers euname eupassword eposition  VALUES  neuname  neupassword  neuposition    DECLARE  lastid INT   SET  lastid     IDENTITY   RETURN  lastid    END TRY   BEGIN CATCH  SELECT ERROR LINE   as errline         ERROR MESSAGE   as errmessage         ERROR SEVERITY   as errsevirity  END CATCH  END   Application configuration file    lt  xml version  1 0  encoding  utf-8   gt   lt configuration gt      lt appSettings gt       lt add key  conStr  value  Data Source User SQLEXPRESS Initial Catalog edata Integrated Security True   gt     lt  appSettings gt   lt  configuration gt    Data Access Layer  DAL    using System  using System Collections Generic  using System Linq  using System Text  using System Threading Tasks  using System Configuration  using System Data  using System Data SqlClient  namespace DAL       public static class DAL               public static SqlConnection conn           static DAL                           conn   new SqlConnection ConfigurationManager AppSettings  conStr   ToString                 conn Open                            Business Logic Layer BLL    using System  using System Collections Generic  using System Linq  using System Text  using System Threading Tasks  using System Data  using System Data SqlClient  using DAL  namespace BLL       public static class BLL                 public static int InsertUser string lastid  params SqlParameter   coll                         int lastInserted   0               try                                 SqlCommand comm   new SqlCommand                     comm Connection   DAL DAL conn                    foreach  var param in coll                                         comm Parameters Add param                                       SqlParameter lastID   new SqlParameter                    lastID ParameterName   lastid                  lastID SqlDbType   SqlDbType Int                  lastID Direction   ParameterDirection ReturnValue                   comm Parameters Add lastID                    comm CommandType   CommandType StoredProcedure                   comm CommandText    InsertNewUser                    comm ExecuteNonQuery                     lastInserted    int comm Parameters lastid  Value                              catch  SqlException ex                                             finally                    if  DAL DAL conn State    ConnectionState Closed                         DAL DAL conn Close                                                             return lastInserted                        Implementation    BLL BLL InsertUser   lastid  new SqlParameter  neuname   Ded                     new SqlParameter  neupassword   Moro ilka                     new SqlParameter  neuposition   Moroz

User · Answer

You say your SQL compiles fine  but I get  Must declare the scalar variable   Password    Also you are trying to return a varchar   b  from your stored procedure  but SQL Server stored procedures can only return integers   When you run the procedure you are going to get the error    Conversion failed when converting the varchar value  x  to data type int

User · Answer

Mehrdad makes some good points  but the main thing I noticed is that you never run the query     SqlParameter retval   sqlcomm Parameters Add   b   SqlDbType VarChar   retval Direction   ParameterDirection ReturnValue  sqlcomm ExecuteNonQuery       MISSING string retunvalue    string sqlcomm Parameters   b   Value

User · Answer

You have mixed up the concept of the Return Value and Output variable  1- Output Variable   Database----- gt   create proc MySP  a varchar 50    b varchar 50  output AS SET  Password     SELECT Password FROM dbo tblUser WHERE Login    a   C  ----- gt    SqlConn Open    sqlcomm CommandType   CommandType StoredProcedure   SqlParameter param   new SqlParameter   a   SqlDbType VarChar   param Direction   ParameterDirection Input   This is optional because Input is the default  param Value   Username  sqlcomm Parameters Add param    SqlParameter outputval   sqlcomm Parameters Add   b   SqlDbType VarChar   outputval  Direction   ParameterDirection Output  NOT ReturnValue    string outputvalue   sqlcomm Parameters   b   Value ToString

[c#] Getting return value from stored procedure in C#

Examples related to c#

Examples related to sql

Examples related to sql-server