Getting return value from stored procedure in C#

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

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.

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;