Command.ExecuteScalar always returns null while the stored procedure in Management Studio is working 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 the CanUserEdit column in the tblUsers column tblUsers is a bit column with a default value of 0. Now when I execute this procedure in Management Studio, it works fine, but when I use command.ExecuteScalar() in my C # code It always returns null . Can someone please tell me what I'm doing wrong here.

Below 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; } } } 

Yours faithfully

Answer 1

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

Try changing the SP as follows:

 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 cannot change the SP, but the code, this solution should read the '@ReturnValue' parameter using ExecuteNonQuery.