How do I use ExecuteScalar with a stored Procedure?

Question

I'm trying to get a count of column records in a Sql database and show the result in a MessageBox.

This is my code:

public DataTable CheckIfNameExist(string name)
{
    con = Connect();
    cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandText = "spCheckIfNameExist";
    cmd.Parameters.AddWithValue("@Name", SqlDbType.NVarChar).Value = name;
    MessageBox.Show(name);

    Int32 totalNames = (Int32) cmd.ExecuteScalar();
    string tNames = totalNames.ToString();

    MessageBox.Show(tNames);
}

And this is my sp:

@Name nvarchar(50) = null
As
Begin
SELECT COUNT(*) from OrdersSent where CustomerName LIKE @Name + '%'
End

Problem:

It always returns 0.

The way you are using parameter in `LIKE` is not correct.... that's why the query itself returns 0 result. [check this](https://stackoverflow.com/questions/14237755/t-sql-and-the-where-like-parameter-clause) — Chetan, Dec 13 '18 at 08:14
Possible duplicate of [T-SQL and the WHERE LIKE %Parameter% clause](https://stackoverflow.com/questions/14237755/t-sql-and-the-where-like-parameter-clause) — Chetan, Dec 13 '18 at 08:15
Try adding cmd.CommandType=CommandType.Procedure before cmd.executeScalar() — ArjunArora, Dec 13 '18 at 08:15
@Arjun It is _CommandType.StoredProcedure_ but I suspect something else here becase that error should result in an Exception — Steve, Dec 13 '18 at 08:17
@Steve, i was just giving him an idea.. and yes i was thinking the same thing.. but why code has not raised the exception in this case instead giving him 0 result — ArjunArora, Dec 13 '18 at 08:20

score 2 · Answer 1 · answered Dec 13 '18 at 08:22

There are a couple of errors in your code: You should write it as:

cmd.CommandText = "spCheckIfNameExist";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = name;

First you need to tell the ADO engine that you are calling a stored procedure and not a simple command text, but you also need to use Add instead of AddWithValue to be precise on the type of the parameter passed to the SP. Your code creates a parameter int becase the second parameter of the AddWithValue is the Value of the parameter not the type.

Salah Akbari · Answer 2 · 2018-12-13T09:42:21.003

You need to specify the type of your command like this:

cmd.CommandText = "spCheckIfNameExist";
cmd.CommandType = CommandType.StoredProcedure;

See also:

What is the benefit of using CommandType.StoredProcedure versus using CommandType.Text?

Although specify the type directly and use the Value property is more better than AddWithValue:

cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = name;

The following article could be also interesting:

https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/

Zohar Peled · Accepted Answer · 2018-12-13T08:34:12.920

You have a few problems in the c# code - the most important is probably this:

cmd.Parameters.AddWithValue("@Name", SqlDbType.NVarChar).Value = name;

Don't use AddWithValue. Use Add.

Also, you didn't specify the command type - the default is Text.
And you are using fields for SqlConnection and SqlCommand - which is also the wrong thing to do. You should create and dispose both of them inside each method you are using them.

A better version of your code would be this:

using(var con = new SqlConnection(ConnectionString))
{
    using(var cmd = new SqlCommand("spCheckIfNameExist", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = name;
        con.Open();
        var tNames = cmd.ExecuteScalar().ToString();
    }
}

Another thing that puzzles me is why a method called CheckIfNameExist returns a DataTable. I would expect it to simply return a bool.

If you really only want to check if the name exists, you can do this better on both the SQL level and the c# level.

A better SQL would be something like this:

SELECT CAST(CASE WHEN EXISTS(
    SELECT 1 
    FROM OrdersSent 
    WHERE CustomerName LIKE @Name + '%'
) THEN 1 ELSE 0 END AS bit)

And on the c# level, bit translates directly to bool, so the code can simple be this:

public bool CheckIfNameExist(string name)
{
    using(var con = new SqlConnection(ConnectionString))
    {
        using(var cmd = new SqlCommand("spCheckIfNameExist", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = name;
            con.Open();
            return (bool)cmd.ExecuteScalar();
        }
    }
}

And another note - you should avoid using the sp prefix for stored procedures.
Microsoft have reserved this prefix for built in system procedures. For more information, read Aaron Bertrand's Is the sp_ prefix still a no-no?, where you'll see that the short answer to this question is "Yes".

The sp_ prefix does not mean what you think it does: most people think sp stands for "stored procedure" when in fact it means "special." Stored procedures (as well as tables and views) stored in master with an sp_ prefix are accessible from any database without a proper reference (assuming a local version does not exist). If the procedure is marked as a system object (using sp_MS_marksystemobject (an undocumented and unsupported system procedure that sets is_ms_shipped to 1), then the procedure in master will execute in the context of the calling database.

[Glad to help :-)](http://meta.stackoverflow.com/questions/291325/how-to-show-appreciation-to-a-user-on-stackoverflow/291327#291327) — Zohar Peled, Dec 13 '18 at 10:34
The reason for returning DataTable is that I have an If statement that depends on the outcome of the ExecuteScalar that in the end will return the DataTable. Thanks for a very informative answer it will come to use! — Neod, Dec 13 '18 at 10:43

How do I use ExecuteScalar with a stored Procedure?

3 Answers3