Getting return value from stored procedure in C#

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?

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;

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.

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.

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

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

There are multiple problems here:

1. 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.
2. 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;
}

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.

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.

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();
     }

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:

<?xml version="1.0" encoding="utf-8"?>
<configuration>

  <appSettings>
    <add key="conStr" value="Data Source=User\SQLEXPRESS;Initial Catalog=edata;Integrated Security=True"/>
  </appSettings>
</configuration>

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

You have mixed up the concept of the Return Value and Output variable. 1- Output Variable:

Database----->:
create proc MySP
@a varchar(50),
@b varchar(50) output
AS
SET @Password = 
(SELECT Password
FROM dbo.tblUser
WHERE Login = @a)

C# ----->:
 
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();

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

<connectionStrings>
    <add name="SqlConnector"
         connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=Databasename;User id=yourusername;Password=yourpassword"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

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

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.

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("@b", SqlDbType.VarChar);
retval.Direction = ParameterDirection.Output;
command.ExecuteNonquery();
string retunvalue = (string)sqlcomm.Parameters["@b"].Value;

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<FileUploadQueryResponse>
              ("dbo.insertFile", 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; }
  }

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;