Procedure expects parameter which was not supplied

Question

In addition to the other answers here, if you've forgotten to put:

cmd.CommandType = CommandType.StoredProcedure;

Then you will also get this error.

I would check my application code and see what value you are setting @template to. I suspect it is null and therein lies the problem.

This issue is indeed usually caused by setting a parameter value to null as HLGEM mentioned above. I thought i would elaborate on some solutions to this problem that i have found useful for the benefit of people new to this problem.

The solution that i prefer is to default the stored procedure parameters to NULL (or whatever value you want), which was mentioned by sangram above, but may be missed because the answer is very verbose. Something along the lines of:

CREATE PROCEDURE GetEmployeeDetails
    @DateOfBirth    DATETIME = NULL,
    @Surname        VARCHAR(20),
    @GenderCode     INT = NULL,
AS

This means that if the parameter ends up being set in code to null under some conditions, .NET will not set the parameter and the stored procedure will then use the default value it has defined. Another solution, if you really want to solve the problem in code, would be to use an extension method that handles the problem for you, something like:

public static SqlParameter AddParameter<T>(this SqlParameterCollection parameters, string parameterName, T value) where T : class
{
    return value == null ? parameters.AddWithValue(parameterName, DBNull.Value) : parameters.AddWithValue(parameterName, value);
}

Matt Hamilton has a good post here that lists some more great extension methods when dealing with this area.

I had a problem where I would get the error when I supplied 0 to an integer param. And found that:

cmd.Parameters.AddWithValue("@Status", 0);

works, but this does not:

cmd.Parameters.Add(new SqlParameter("@Status", 0));