过程要求未提供的参数

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

当访问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.

当我通过。net的数据连接(System.data.SqlClient)调用一个带有参数的存储过程时，就会发生这种情况，尽管我提供了这个参数。这是我的代码。

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

编辑:事实证明，模板是空的，因为我是从一个通过URL传递的参数中获取它的值的，我把URL参数传递搞砸了(我使用@ for而不是&)

10 个解决方案

#1

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.

我将检查我的应用程序代码，看看您将@template设置为什么值。我怀疑它是无效的，这就是问题所在。

#2

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

除了这里的其他答案，如果你忘了写:

cmd.CommandType = CommandType.StoredProcedure;

Then you will also get this error.

然后你也会得到这个错误。

#3

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.

这个问题通常是由将参数值设置为null引起的，如上面提到的HLGEM。我想我应该对这个问题的一些解决办法进行详细的阐述，我发现这些方法对新接触这个问题的人是有用的。

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:

我喜欢的解决方案是将存储过程参数默认为NULL(或您想要的任何值)，这在前面的sangram中提到过，但是可能会被忽略，因为答案非常冗长。类似于:

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:

这意味着，如果在某些情况下，在代码中将参数设置为null， . net将不会设置参数，存储过程将使用它定义的默认值。如果你真的想用代码解决问题，另一个解决方案是使用扩展方法来为你解决问题，比如:

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.

Matt Hamilton在这里有一个很好的帖子，列出了在处理这个区域时一些更重要的扩展方法。

#4

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

我有一个问题，当我将0提供给一个整数参数时，会得到错误。,发现:

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

works, but this does not:

有效，但这不是:

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

#5

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

这里，我将存储过程的输入Params初始化为null，如下所示

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

我希望这对陷入类似陷阱的人有帮助。

#6

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.

对于我的情况，我必须通过DBNULL。值(使用if else条件)来自未定义为null但值为null的存储过程参数的代码。

#7

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

如果模板没有被设置(例如== =null)，这个错误也会被引发。

More comments:

更多的评论:

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

如果在添加参数时知道参数值，也可以使用AddWithValue

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

EXEC不是必需的。您可以在SELECT中直接引用@template参数。

#8

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

首先，为什么是EXEC?不应该,只是

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

当前的SP没有意义?特别是，这将查找匹配@template的列，而不是@template的varchar值。例如，如果@template是'Column_Name'，它会在TABLE_NAME = Column_Name的地方进行搜索，这是非常罕见的(将表和列命名为相同的)。

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.

此外，如果您必须使用动态SQL，您应该使用EXEC sp_ExecuteSQL(将值保存为参数)来防止注入攻击(而不是将输入连接起来)。但在这种情况下没有必要。

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

再看看实际的问题——从表面上看还可以;你确定你没有不同的SP的副本吗?这是一个常见的错误……

#9

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.

我今天遇到这个错误，当时空值被传递给存储过程的参数。通过添加默认值= null修改存储过程，我可以轻松地进行修复。

#10

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.

然后发生了错误。