.net 过程需要未提供的参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/368642/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Procedure expects parameter which was not supplied
提问by Tony Peterson
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 与 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();
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 和而不是&）
采纳答案by HLGEM
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 设置为什么值。我怀疑它是空的，这就是问题所在。
回答by Brian
In addition to the other answers here, if you've forgotten to put:
除了这里的其他答案外，如果您忘记输入：
cmd.CommandType = CommandType.StoredProcedure;
Then you will also get this error.
然后你也会得到这个错误。
回答by Xcalibur
This issue is indeed usually caused by setting a parameter value to null as HLGEMmentioned 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.
这个问题确实通常是由于上面提到的HLGEM将参数值设置为 null 引起的。我想我会详细说明这个问题的一些解决方案，我发现这些解决方案对不熟悉这个问题的人很有用。
The solution that i prefer is to default the stored procedure parameters to NULL (or whatever value you want), which was mentioned by sangramabove, 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 herethat lists some more great extension methods when dealing with this area.
回答by Anders Rune Jensen
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));
回答by rafoo
For my case, I had to pass DBNULL.Value(using if else condition) from code for stored procedures parameter that are not defined nullbut value is null.
就我而言，我必须DBNULL.Value从代码中传递（使用 if else 条件）未定义null但值为 null.
回答by sangram
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
这里我将存储过程的输入参数初始化为 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.
我希望这对陷入类似陷阱的人有所帮助。
回答by devio
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 参数。
回答by Marc Gravell
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 columnmatching @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'，它会搜索WHERE TABLE_NAME = Column_Name，这是非常罕见的（表和列的名称相同）。
Also, if you dohave 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 副本吗？这是一个常见的错误...
回答by Haitian Programmer
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.
然后错误发生。
回答by user4249282
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 来更改存储过程，从而轻松修复。
