C# Stored procedure or function expects parameter which is not supplied

Question

I am fairly new to C# and I'm trying to set up call to a stored procedure in my database which takes one parameter.

I get the error "Procedure or function 'SP_getName' expects parameter '@username', which was not supplied. "

My Stored procedure works ok when I supply it with the parameter and I run it via SQL management studio.

GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[SP_getName]
    @username = 'bob101'

SELECT  'Return Value' = @return_value

GO

However when I try and call it the error is with how I'm passing the parameter in, but I can't spot what the issue is.

           //create a sql command object to hold the results of the query
            SqlCommand cmd = new SqlCommand();

            //and a reader to process the results
            SqlDataReader reader;

            //Instantiate return string
            string returnValue = null;

            //execute the stored procedure to return the results
            cmd.CommandText = "SP_getName";

            //set up the parameters for the stored procedure
            cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = "bob101";

            cmd.CommandType = CommandType.Text;
            cmd.Connection = this.Connection;

            // then call the reader to process the results
            reader = cmd.ExecuteReader();

Any help in spotting my error would be greatly appreciated!

I've also tried looking at these two posts, but I haven't had any luck:

Stored procedure or function expects parameter which is not supplied

Procedure or function expects parameter, which was not supplied

Thanks!

BTW, you should not use SP_ as a prefix for your procedures. That is reserved for system procs. If MS releases a proc with the same name at some point yours will not run anymore. Honestly you should not use a prefix at all, they add nothing for clarity. — Sean Lange

GarethD GarethD · Accepted Answer · 2014-10-31T12:00:16

You have stated:

cmd.CommandType = CommandType.Text;

Therefore you are simply executing:

SP_getName

Which works because it is the first statement in the batch, so you can call the procedure without EXECUTE, but you aren't actually including the parameter. Change it to

cmd.CommandType = CommandType.StoredProcedure;

Or you can change your CommandText to:

EXECUTE SP_getName @username;

As a side note you should Avoid using the prefix 'sp_' for your stored procedures

And a further side note would be to use using with IDisposable objects to ensure they are disposed of correctly:

using (var connection = new SqlConnection("ConnectionString"))
using (var cmd = new new SqlCommand("SP_getName", connection))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = "bob101";
    connection.Open();
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Do something 
        }
    }
}

C# Stored procedure or function expects parameter which is not supplied

3 Answers