CODEWITHSUNDEEP
c#sqlstored-procedures
Learning Curve
Learning Curve

Reputation: 1569

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

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

Upvotes: 5

Views: 18708

Answers (2)

Vance McCorkle
Vance McCorkle

Reputation: 493

Use a scaler function instead of a stored procedure. SQL functions are much easier to setup and they return a scaler value by default so no 'OUTPUT' or return variable declarations necessary. Example:

CREATE FUNCTION [dbo].[ScalerFnExample]()
RETURNS VARCHAR(50)
AS
   BEGIN
      RETURN (SELECT TOP 1 Thing FROM Examples.dbo.Stuff)
   END

C# Example:

    public string SqlScalerFnExample()
    {
        string retVal = default;

        try
        {
            using (SqlConnection sqlConnection = new SqlConnection("Server=127.0.0.1;Database=Examples;Trusted_Connection=True;"))
            {
                SqlCommand userFn = new SqlCommand("SELECT dbo.ScalerFnExample()", sqlConnection);

                sqlConnection.Open();

                retVal = (string)userFn.ExecuteScalar();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"SqlScalerFnExample() - Exception: {ex.Message}");
        }

        return retVal;
    }

Upvotes: 0

Dan
Dan

Reputation: 1080

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.

Upvotes: 17

Related Questions