过程期望未提供的参数

在访问 SQLServer 中的存储过程时出现错误

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

当我使用一个参数通过。Net 到 sql(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();

我的存储过程是:

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

我在想我哪里做错了。

编辑: 事实证明，Template 是空的，因为我从通过 URL 传递的参数中获得了它的值，并且我搞砸了 URL 参数的传递(我使用了 @来代替了 &)

216358 次浏览
小开

首先，为什么这是一个 EXEC? 它不应该只是

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

现在的 SP 说不通吗？特别是，它将查找与@template 匹配的 专栏，而不是@template 的 varchar 值。也就是说，如果@template 是 'Column_Name'，它将搜索 WHERE TABLE_NAME = Column_Name，这是非常罕见的(表和列的名称相同)。

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

关于实际的问题-它看起来确定从一瞥; 你确定你没有一个不同的副本 SP 挂在左右？这是一个常见的错误..。

小开
最佳答案

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.

小开

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

更多评论:

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

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

小开

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

cmd.CommandType = CommandType.StoredProcedure;

Then you will also get this error.

小开

我在调用存储过程时遇到过类似的问题

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;

它动态地构造我上面调用的搜索查询:

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

然后经过大量的思考，我修改了存储过程:

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;

在这里，我将存储过程的输入参数初始化为 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

对我很有效。

我希望这对陷入类似困境的人有所帮助。

小开

这个问题通常是由上面提到的将参数值设置为 null 引起的。我想我会详细阐述一些解决这个问题的办法，我已经发现有益于人们的新的这个问题。

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

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

这意味着，如果在某些条件下，参数最终在代码中被设置为 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 有一篇很好的文章 给你，列出了一些更好的扩展方法来处理这个领域。

小开

我遇到了一个问题，当我向一个整数参数提供0时，我会得到一个错误。发现:

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

有效，但这不是:

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

我也遇到过同样的问题，要解决这个问题，只需向参数集合中添加与存储过程中完全相同的参数名即可。

例子

假设您创建了一个存储过程:

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

因此，请确保参数的名称与存储过程中的名称完全一致

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

如果你去的话

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

然后错误就发生了。

小开

对于我的情况，我必须从代码中为未定义为 null但值为 null的存储过程参数传递 DBNULL.Value(使用 if else 条件)。

小开

今天，当空值传递给存储过程的参数时，我遇到了这个错误。我可以通过添加 default value = null 来修改存储过程，从而轻松地解决这个问题。

小开

我也遇到了同样的问题。我的参数是空值。所以我通过处理 null 值来解决。如果有人不确定运行时值并想处理 null，那么只需使用这个。(你不想改变 SP/函数。)例如。

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

以防有人发现，谷歌一下。如果在定义了默认参数值(这应该允许您在不显式传递该参数的情况下运行过程)的情况下仍然出现这个错误，请仔细检查是否确实设置了默认值，如下所示:

@someTextParameter NVARCHAR(100) NULL

只允许参数接受 NULL作为输入，而这个:

@someTextParameter NVARCHAR(100) = NULL

实际上声明参数是可选的。

这是一个愚蠢的错误，忽视了我花了半个小时;)


提交答案