c# - Command.ExecuteScalar always return null while Stored Procedure in Management Studio runs fine

Question

I have the following SQL stored procedure with one input parameter and one out parameter.

CREATE PROCEDURE [dbo].[spCanUserEdit]
(
@username nvarchar(255)
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @CanEdit bit

SELECT
    @CanEdit = CanUserEdit
FROM tblUsers
WHERE username = LOWER(@username)
RETURN SELECT @CanEdit
END
GO

In the stored procedure above CanUserEdit column in tblUsers is bit type column and with default value to 0. Now when I execute this procedure in Management Studio it runs fine but when i use command.ExecuteScalar() in my C# code, it always returns null. Could anyone please tell me what I am doing wrong here.

Following is my C# method

public static bool CanUserEdit(string userName)
{
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[Constants.ConnectionStringName].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "spCanUserEdit";
            cmd.Connection = conn;

            cmd.Parameters.Add(new SqlParameter("@username", userName));

            conn.Open();
            bool canEdit = (bool)cmd.ExecuteScalar();

            return canEdit;
        }
    }
}

Kind Regards

Answer 1

The problem is in the way you return data. If you want to use ExecuteScalar, you should not RETURN but instead simply SELECT.

Try to change the SP as following:

CREATE PROCEDURE [dbo].[spCanUserEdit]
(
 @username nvarchar(255)
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @CanEdit bit

SELECT
    @CanEdit = CanUserEdit
FROM tblUsers
WHERE username = LOWER(@username)

SELECT @CanEdit

RETURN 0
END
GO

If you can't change the SP, but the code, the solution is to read parameter '@ReturnValue' with ExecuteNonQuery.