[.net] Procedure expects parameter which was not supplied

I'm getting the error when accessing a Stored Procedure in SQL Server

Server Error in '/' Application.
Procedure or function 'ColumnSeek' expects parameter '@template', which was not supplied. 

This is happening when I call a Stored Procedure with a parameter through .net's data connection to sql (System.data.SqlClient), even though I am supplying the parameter. Here is my code.

SqlConnection sqlConn = new SqlConnection(connPath);
sqlConn.Open();

//METADATA RETRIEVAL
string sqlCommString = "QCApp.dbo.ColumnSeek";
SqlCommand metaDataComm = new SqlCommand(sqlCommString, sqlConn);
metaDataComm.CommandType = CommandType.StoredProcedure;
SqlParameter sp = metaDataComm.Parameters.Add("@template",SqlDbType.VarChar,50);
sp.Value = Template;

SqlDataReader metadr = metaDataComm.ExecuteReader();

And my Stored Procedure is:

   USE [QCApp]
   GO
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO

   ALTER PROCEDURE [dbo].[ColumnSeek] 
       @template varchar(50)
   AS
   EXEC('SELECT Column_Name, Data_Type 
   FROM [QCApp].[INFORMATION_SCHEMA].[COLUMNS] 
   WHERE TABLE_NAME = ' + @template);

I'm trying to figure out what I'm doing wrong here.

Edit: As it turns out, Template was null because I was getting its value from a parameter passed through the URL and I screwed up the url param passing (I was using @ for and instead of &)

For my case, I had to pass DBNULL.Value(using if else condition) from code for stored procedures parameter that are not defined null but value is null.

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 came across same issue. And my parameter was having null value. So I resolved by handling null value. If someone is not sure runtime value and want to handle null then simply use this. (And you don't want to change the SP/function.) E.g.

sp.Value = Template ?? (object)DBNull.Value;

First - why is that an EXEC? Shouldn't that just be

AS
SELECT Column_Name, ...
FROM ...
WHERE TABLE_NAME = @template

The current SP doesn't make sense? In particular, that would look for a column matching @template, not the varchar value of @template. i.e. if @template is 'Column_Name', it would search WHERE TABLE_NAME = Column_Name, which is very rare (to have table and column named the same).

Also, if you do have to use dynamic SQL, you should use EXEC sp_ExecuteSQL (keeping the values as parameters) to prevent from injection attacks (rather than concatenation of input). But it isn't necessary in this case.

Re the actual problem - it looks OK from a glance; are you sure you don't have a different copy of the SP hanging around? This is a common error...

If Template is not set (i.e. ==null), this error will be raised, too.

More comments:

If you know the parameter value by the time you add parameters, you can also use AddWithValue

The EXEC is not required. You can reference the @template parameter in the SELECT directly.

I came across this error today when null values were passed to my stored procedure's parameters. I was able easily fix by altering the stored procedure by adding default value = null.

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

It is necessary to tell that a Stored Proc is being called:

comm.CommandType = CommandType.StoredProcedure;

I had the same issue, to solve it just add exactly the same parameter name to your parameter collection as in your stored procedures.

Example

Let's say you create a stored procedure:

create procedure up_select_employe_by_ID 
     (@ID int) 
as
    select * 
    from employe_t 
    where employeID = @ID

So be sure to name your parameter exactly as it is in your stored procedure this would be

cmd.parameter.add("@ID", sqltype,size).value = @ID

if you go

cmd.parameter.add("@employeID", sqltype,size).value = @employeid 

then the error happens.

First - why is that an EXEC? Shouldn't that just be

AS
SELECT Column_Name, ...
FROM ...
WHERE TABLE_NAME = @template

The current SP doesn't make sense? In particular, that would look for a column matching @template, not the varchar value of @template. i.e. if @template is 'Column_Name', it would search WHERE TABLE_NAME = Column_Name, which is very rare (to have table and column named the same).

Also, if you do have to use dynamic SQL, you should use EXEC sp_ExecuteSQL (keeping the values as parameters) to prevent from injection attacks (rather than concatenation of input). But it isn't necessary in this case.

Re the actual problem - it looks OK from a glance; are you sure you don't have a different copy of the SP hanging around? This is a common error...

I come across similar problem while calling stored procedure

CREATE PROCEDURE UserPreference_Search
    @UserPreferencesId int,
    @SpecialOfferMails char(1),
    @NewsLetters char(1),
    @UserLoginId int,
    @Currency varchar(50)
AS
DECLARE @QueryString nvarchar(4000)

SET @QueryString = 'SELECT UserPreferencesId,SpecialOfferMails,NewsLetters,UserLoginId,Currency FROM UserPreference'
IF(@UserPreferencesId IS NOT NULL)
BEGIN
SET @QueryString = @QueryString + ' WHERE UserPreferencesId = @DummyUserPreferencesId';
END

IF(@SpecialOfferMails IS NOT NULL)
BEGIN
SET @QueryString = @QueryString + ' WHERE SpecialOfferMails = @DummySpecialOfferMails';
END

IF(@NewsLetters IS NOT NULL)
BEGIN
SET @QueryString = @QueryString + ' WHERE NewsLetters = @DummyNewsLetters';
END

IF(@UserLoginId IS NOT NULL)
BEGIN
SET @QueryString = @QueryString + ' WHERE UserLoginId = @DummyUserLoginId';
END

IF(@Currency IS NOT NULL)
BEGIN
SET @QueryString = @QueryString + ' WHERE Currency = @DummyCurrency';
END

EXECUTE SP_EXECUTESQL @QueryString
                     ,N'@DummyUserPreferencesId int, @DummySpecialOfferMails char(1), @DummyNewsLetters char(1), @DummyUserLoginId int, @DummyCurrency varchar(50)'
                     ,@DummyUserPreferencesId=@UserPreferencesId
                     ,@DummySpecialOfferMails=@SpecialOfferMails
                     ,@DummyNewsLetters=@NewsLetters
                     ,@DummyUserLoginId=@UserLoginId
                     ,@DummyCurrency=@Currency;

Which dynamically constructing the query for search I was calling above one by:

public DataSet Search(int? AccessRightId, int? RoleId, int? ModuleId, char? CanAdd, char? CanEdit, char? CanDelete, DateTime? CreatedDatetime, DateTime? LastAccessDatetime, char? Deleted)
    {
        dbManager.ConnectionString = ConfigurationManager.ConnectionStrings["MSSQL"].ToString();
        DataSet ds = new DataSet();
        try
        {
            dbManager.Open();
            dbManager.CreateParameters(9);
            dbManager.AddParameters(0, "@AccessRightId", AccessRightId, ParameterDirection.Input);
            dbManager.AddParameters(1, "@RoleId", RoleId, ParameterDirection.Input);
            dbManager.AddParameters(2, "@ModuleId", ModuleId, ParameterDirection.Input);
            dbManager.AddParameters(3, "@CanAdd", CanAdd, ParameterDirection.Input);
            dbManager.AddParameters(4, "@CanEdit", CanEdit, ParameterDirection.Input);
            dbManager.AddParameters(5, "@CanDelete", CanDelete, ParameterDirection.Input);
            dbManager.AddParameters(6, "@CreatedDatetime", CreatedDatetime, ParameterDirection.Input);
            dbManager.AddParameters(7, "@LastAccessDatetime", LastAccessDatetime, ParameterDirection.Input);
            dbManager.AddParameters(8, "@Deleted", Deleted, ParameterDirection.Input);
            ds = dbManager.ExecuteDataSet(CommandType.StoredProcedure, "AccessRight_Search");
            return ds;
        }
        catch (Exception ex)
        {
        }
        finally
        {
            dbManager.Dispose();
        }
        return ds;
    }

Then after lot of head scratching I modified stored procedure to:

ALTER PROCEDURE [dbo].[AccessRight_Search]
    @AccessRightId int=null,
    @RoleId int=null,
    @ModuleId int=null,
    @CanAdd char(1)=null,
    @CanEdit char(1)=null,
    @CanDelete char(1)=null,
    @CreatedDatetime datetime=null,
    @LastAccessDatetime datetime=null,
    @Deleted char(1)=null
AS
DECLARE @QueryString nvarchar(4000)
DECLARE @HasWhere bit
SET @HasWhere=0

SET @QueryString = 'SELECT a.AccessRightId, a.RoleId,a.ModuleId, a.CanAdd, a.CanEdit, a.CanDelete, a.CreatedDatetime, a.LastAccessDatetime, a.Deleted, b.RoleName, c.ModuleName FROM AccessRight a, Role b, Module c WHERE a.RoleId = b.RoleId AND a.ModuleId = c.ModuleId'

SET @HasWhere=1;

IF(@AccessRightId IS NOT NULL)
    BEGIN
        IF(@HasWhere=0) 
            BEGIN
                SET @QueryString = @QueryString + ' WHERE a.AccessRightId = @DummyAccessRightId';
                SET @HasWhere=1;
            END
        ELSE                SET @QueryString = @QueryString + ' AND a.AccessRightId = @DummyAccessRightId';
    END

IF(@RoleId IS NOT NULL)
    BEGIN
        IF(@HasWhere=0)
            BEGIN   
                SET @QueryString = @QueryString + ' WHERE a.RoleId = @DummyRoleId';
                SET @HasWhere=1;
            END
        ELSE            SET @QueryString = @QueryString + ' AND a.RoleId = @DummyRoleId';
    END

IF(@ModuleId IS NOT NULL)
BEGIN
    IF(@HasWhere=0) 
            BEGIN   
                SET @QueryString = @QueryString + ' WHERE a.ModuleId = @DummyModuleId';
                SET @HasWhere=1;
            END
    ELSE SET @QueryString = @QueryString + ' AND a.ModuleId = @DummyModuleId';
END

IF(@CanAdd IS NOT NULL)
BEGIN
    IF(@HasWhere=0) 
            BEGIN       
                SET @QueryString = @QueryString + ' WHERE a.CanAdd = @DummyCanAdd';
                SET @HasWhere=1;
            END
    ELSE SET @QueryString = @QueryString + ' AND a.CanAdd = @DummyCanAdd';
END

IF(@CanEdit IS NOT NULL)
BEGIN
    IF(@HasWhere=0) 
        BEGIN
            SET @QueryString = @QueryString + ' WHERE a.CanEdit = @DummyCanEdit';
            SET @HasWhere=1;
        END
    ELSE SET @QueryString = @QueryString + ' AND a.CanEdit = @DummyCanEdit';
END

IF(@CanDelete IS NOT NULL)
BEGIN
    IF(@HasWhere=0) 
        BEGIN
            SET @QueryString = @QueryString + ' WHERE a.CanDelete = @DummyCanDelete';
            SET @HasWhere=1;
        END
    ELSE SET @QueryString = @QueryString + ' AND a.CanDelete = @DummyCanDelete';
END

IF(@CreatedDatetime IS NOT NULL)
BEGIN
    IF(@HasWhere=0) 
    BEGIN
        SET @QueryString = @QueryString + ' WHERE a.CreatedDatetime = @DummyCreatedDatetime';
        SET @HasWhere=1;
    END
    ELSE SET @QueryString = @QueryString + ' AND a.CreatedDatetime = @DummyCreatedDatetime';
END

IF(@LastAccessDatetime IS NOT NULL)
BEGIN
    IF(@HasWhere=0) 
        BEGIN
            SET @QueryString = @QueryString + ' WHERE a.LastAccessDatetime = @DummyLastAccessDatetime';
            SET @HasWhere=1;
        END
    ELSE SET @QueryString = @QueryString + ' AND a.LastAccessDatetime = @DummyLastAccessDatetime';
END

IF(@Deleted IS NOT NULL)
BEGIN
  IF(@HasWhere=0)   
    BEGIN
        SET @QueryString = @QueryString + ' WHERE a.Deleted = @DummyDeleted';
        SET @HasWhere=1;
    END
  ELSE SET @QueryString = @QueryString + ' AND a.Deleted = @DummyDeleted';
END

PRINT @QueryString

EXECUTE SP_EXECUTESQL @QueryString
                      ,N'@DummyAccessRightId int, @DummyRoleId int, @DummyModuleId int, @DummyCanAdd char(1), @DummyCanEdit char(1), @DummyCanDelete char(1), @DummyCreatedDatetime datetime, @DummyLastAccessDatetime datetime, @DummyDeleted char(1)'
                      ,@DummyAccessRightId=@AccessRightId
                      ,@DummyRoleId=@RoleId
                      ,@DummyModuleId=@ModuleId
                      ,@DummyCanAdd=@CanAdd
                      ,@DummyCanEdit=@CanEdit
                      ,@DummyCanDelete=@CanDelete
                      ,@DummyCreatedDatetime=@CreatedDatetime
                      ,@DummyLastAccessDatetime=@LastAccessDatetime
                      ,@DummyDeleted=@Deleted;

HERE I am Initializing the Input Params of Stored Procedure to null as Follows

    @AccessRightId int=null,
@RoleId int=null,
@ModuleId int=null,
@CanAdd char(1)=null,
@CanEdit char(1)=null,
@CanDelete char(1)=null,
@CreatedDatetime datetime=null,
@LastAccessDatetime datetime=null,
@Deleted char(1)=null

that did the trick for Me.

I hope this will be helpfull to someone who fall in similar trap.

For my case, I had to pass DBNULL.Value(using if else condition) from code for stored procedures parameter that are not defined null but value is null.

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

cmd.CommandType = CommandType.StoredProcedure;

Then you will also get this error.

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

First - why is that an EXEC? Shouldn't that just be

AS
SELECT Column_Name, ...
FROM ...
WHERE TABLE_NAME = @template

The current SP doesn't make sense? In particular, that would look for a column matching @template, not the varchar value of @template. i.e. if @template is 'Column_Name', it would search WHERE TABLE_NAME = Column_Name, which is very rare (to have table and column named the same).

Also, if you do have to use dynamic SQL, you should use EXEC sp_ExecuteSQL (keeping the values as parameters) to prevent from injection attacks (rather than concatenation of input). But it isn't necessary in this case.

Re the actual problem - it looks OK from a glance; are you sure you don't have a different copy of the SP hanging around? This is a common error...

I came across this error today when null values were passed to my stored procedure's parameters. I was able easily fix by altering the stored procedure by adding default value = null.

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.

It is necessary to tell that a Stored Proc is being called:

comm.CommandType = CommandType.StoredProcedure;

If Template is not set (i.e. ==null), this error will be raised, too.

More comments:

If you know the parameter value by the time you add parameters, you can also use AddWithValue

The EXEC is not required. You can reference the @template parameter in the SELECT directly.

I came across same issue. And my parameter was having null value. So I resolved by handling null value. If someone is not sure runtime value and want to handle null then simply use this. (And you don't want to change the SP/function.) E.g.

sp.Value = Template ?? (object)DBNull.Value;

I had the same issue, to solve it just add exactly the same parameter name to your parameter collection as in your stored procedures.

Example

Let's say you create a stored procedure:

create procedure up_select_employe_by_ID 
     (@ID int) 
as
    select * 
    from employe_t 
    where employeID = @ID

So be sure to name your parameter exactly as it is in your stored procedure this would be

cmd.parameter.add("@ID", sqltype,size).value = @ID

if you go

cmd.parameter.add("@employeID", sqltype,size).value = @employeid 

then the error happens.

First - why is that an EXEC? Shouldn't that just be

AS
SELECT Column_Name, ...
FROM ...
WHERE TABLE_NAME = @template

The current SP doesn't make sense? In particular, that would look for a column matching @template, not the varchar value of @template. i.e. if @template is 'Column_Name', it would search WHERE TABLE_NAME = Column_Name, which is very rare (to have table and column named the same).

Also, if you do have to use dynamic SQL, you should use EXEC sp_ExecuteSQL (keeping the values as parameters) to prevent from injection attacks (rather than concatenation of input). But it isn't necessary in this case.

Re the actual problem - it looks OK from a glance; are you sure you don't have a different copy of the SP hanging around? This is a common error...