Read the return value of SQL Server stored procedure in C#?

Question

I want to catch on -1, 0, 1 values returning from the SQL Server stored procedure in C#.

SQL Server:

IF EXISTS(SELECT * FROM _Users WHERE UserName = @UserName AND [Password] != @Password)
BEGIN
    RETURN -1
END

IF EXISTS(SELECT * FROM _Users WHERE UserName != @UserName AND [Password] != @Password)
BEGIN
    RETURN 0
END

IF EXISTS(SELECT * FROM _Users WHERE UserName = @UserName AND [Password]= @Password)
BEGIN
    RETURN 1
END

C#

Are we able to change the returning value of the code below just by making as I want to?

public User getUser(string name, string password)
{
    User user = null;

    using (var connection = Database.GetConnection())
    {
        var command = new SqlCommand("SP_GetUser @UserName, @Password", connection);
        command.Parameters.Add(new SqlParameter("UserName", name));
        command.Parameters.Add(new SqlParameter("Password", password));

        connection.Open();

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                user = new User();
                user.UserId = Convert.ToInt32(reader["UserId"]);
                user.userName = Convert.ToString(reader["UserName"]);
                user.password = Convert.ToString(reader["Password"]);
            }
        }

        connection.Close();
    }

    return user; // I want to return -1, 0, 1
}

Does this answer your question? [Calling stored procedure with return value](https://stackoverflow.com/questions/6210027/calling-stored-procedure-with-return-value) — Dale K, Jul 29 '20 at 03:40
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Jul 29 '20 at 04:02
https://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database — mjwills, Jul 29 '20 at 04:30

Dale K · Answer 1 · 2020-07-29T04:08:55.630

SqlParameter returnValueParam = new SqlParameter() { Direction = ParameterDirection.ReturnValue };
command.Parameters.Add(returnValueParam);

// Read everything from your DataReader before trying to obtain the return value
// In fact after you close the connection

var returnValue = returnValueParam.Value;

Notes:

Ensure you set command.CommandType = CommandType.StoredProcedure; (and remove the parameters from the CommandText as it can only be the SP name when using CommandType.StoredProcedure).
You should always specify the datatype and scale/precision (if relevant) when creating a SqlParameter as there can be unexpected side effects allowing the datatype to be set automatically. Its also best practice to fully name the parameter, including the @, So

new SqlParameter("UserName", name);

should really be

// Where the type and length match your SP
new SqlParameter("@UserName", SqlDbType.NVarChar, 128) { Value = name };

Technically the return value of a stored procedure is for the "execution status" of the stored procedure. You would normally use an OUTPUT parameter to return user data. However in my opinion your use-case is as good as any.
As noted by @marc_s: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all!

Read the return value of SQL Server stored procedure in C#?

1 Answers1