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

From stackoverflow

• 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...

  Tony Peterson : It still doesn't work with that change. I had the exec because I was working previously with a proc where the from clause was supplied from a param so I got thinking wrongly on this one. But I still get the error with just the select

  Marc Gravell : very curious; perhaps treble-check for typos?

• 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.

  Marc Gravell : Ah, the old null vs DbNull.Value chestnut...

  Marc Gravell : (+1 btw, I'm pretty sure that will fix it)

  Tony Peterson : Yeah, Template was null, I forgot to set it earlier.

• 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.

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

  cmd.commandtype = commandtype.storedprocedure

  Then you will also get this error.

  Sohnee : +1 Great answer.