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.

In C#, I want to execute this query and get the return value.

My code is as 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?

Thanks

Answer 1

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.

Answer 2

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

string retunvalue = (string)sqlcomm.Parameters["@b"].Value;

Answer 3

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

Answer 4

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.

Answer 5

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.