Procedure expects parameter which was not supplied

Tony_Peterson_26140 0
Viewed 212205

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

stored-procedures sql-server .net
12 Answers

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.

HLGEM_90340

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.

devio_213360

Just in case someone comes across, googling. If you're getting this error up despite having the default parameter value defined (which should allow you to run the procedure without explicitly passing that parameter), double check if you actually did set the default value, as this:

@someTextParameter NVARCHAR(100) NULL

only lets the parameter accept NULL as input, while this:

@someTextParameter NVARCHAR(100) = NULL

is actually declaring parameter optional.

It's a silly mistake, overlooking which costed me half an hour ;)

user_12849020

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

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

Marc_Gravell_233540
Related