存储过程上的 OdbcCommand - “未提供参数”输出参数错误
我正在尝试执行存储过程(通过 ODBC 驱动程序针对 SQL Server 2005),但收到以下错误:
过程或函数“GetNodeID”需要参数“@ID”,但未提供该参数。
@ID 是我的过程的 OUTPUT 参数,在存储过程中指定了一个输入 @machine 并设置为 null:
ALTER PROCEDURE [dbo].[GetNodeID]
@machine nvarchar(32) = null,
@ID int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM Nodes WHERE NodeName=@machine)
BEGIN
SELECT @ID = (SELECT NodeID FROM Nodes WHERE NodeName=@machine)
END
ELSE
BEGIN
INSERT INTO Nodes (NodeName) VALUES (@machine)
SELECT @ID = (SELECT NodeID FROM Nodes WHERE NodeName=@machine)
END
END
以下是我用来设置参数和调用该过程的代码:
OdbcCommand Cmd = new OdbcCommand("GetNodeID", _Connection);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("@machine", OdbcType.NVarChar);
Cmd.Parameters["@machine"].Value = Environment.MachineName.ToLower();
Cmd.Parameters.Add("@ID", OdbcType.Int);
Cmd.Parameters["@ID"].Direction = ParameterDirection.Output;
Cmd.ExecuteNonQuery();
_NodeID = (int)Cmd.Parameters["@Count"].Value;
我已经还尝试使用 Cmd.ExecuteScalar 但没有成功。如果我在执行命令之前中断,我可以看到 @machine 有一个值。
如果我直接从 Management Studio 执行该过程,它会正常工作。
有什么想法吗?谢谢
I'm trying to execute a stored procedure (against SQL Server 2005 through the ODBC driver) and I recieve the following error:
Procedure or Function 'GetNodeID' expects parameter '@ID', which was not supplied.
@ID is the OUTPUT parameter for my procedure, there is an input @machine which is specified and is set to null in the stored procedure:
ALTER PROCEDURE [dbo].[GetNodeID]
@machine nvarchar(32) = null,
@ID int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM Nodes WHERE NodeName=@machine)
BEGIN
SELECT @ID = (SELECT NodeID FROM Nodes WHERE NodeName=@machine)
END
ELSE
BEGIN
INSERT INTO Nodes (NodeName) VALUES (@machine)
SELECT @ID = (SELECT NodeID FROM Nodes WHERE NodeName=@machine)
END
END
The following is the code I'm using to set the parameters and call the procedure:
OdbcCommand Cmd = new OdbcCommand("GetNodeID", _Connection);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("@machine", OdbcType.NVarChar);
Cmd.Parameters["@machine"].Value = Environment.MachineName.ToLower();
Cmd.Parameters.Add("@ID", OdbcType.Int);
Cmd.Parameters["@ID"].Direction = ParameterDirection.Output;
Cmd.ExecuteNonQuery();
_NodeID = (int)Cmd.Parameters["@Count"].Value;
I've also tried using Cmd.ExecuteScalar with no success. If I break before I execute the command, I can see that @machine has a value.
If I execute the procedure directly from Management Studio, it works correctly.
Any thoughts? Thanks
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
尝试将 : 替换
为 :
更多信息 :
http://support.microsoft.com/kb/310130
Try replacing :
With :
More info :
http://support.microsoft.com/kb/310130
我不太确定你的意思
在您的过程签名中,这一行:
并不意味着您在过程中将
@machine
设置为 null - 这意味着您正在分配一个默认值,以供参数使用缺失(在本例中,null
是用于缺失参数的值)。如果您在不传递任何参数的情况下调用此存储过程,则会发生有关
@ID
丢失的错误(@machine
不会被标记为问题,因为它已定义的默认值)。您的代码示例对我来说看起来不错 - 您确定存储过程没有从程序中的其他地方(没有添加参数的地方)调用吗?I'm not exactly sure what you mean by
In your proc's signature, this line:
doesn't mean that you're setting
@machine
to null inside the proc - it means you're assigning a default value to be used in case the parameter is missing (in this case,null
is the value to be used for a missing param).Getting the error about
@ID
being missing would happen if you were calling this stored procedure without passing any parameters at all (@machine
would not be flagged as a problem since it has a default value defined). Your code example looks fine to me - are you sure the stored proc isn't being called from somewhere else in your program (somewhere where no parameters are being added)?带有输入参数和 ODBC 连接的存储过程:
创建存储过程:
此代码适用于
SQL Server。
Stored procedure with input parameters and ODBC Connection:
create a stored procedure:
This code works in
SQL Server.