sql server存储过程中的输出参数问题 [英] Output parameter problem in sql server store procedure

问题描述

大家好

我正在使用SQL服务器而且我创建了一个商店程序，但我收到了以下错误

Hi all
I am using SQL server and I created a store procedure but I am getting following error

Server: Msg 201, Level 16, State 4, Procedure SP_New_OrderNo, Line 0
Procedure 'SP_New_OrderNo' expects parameter '@ERROR', which was not supplied.

可以任何一个请帮帮我？



Bellow我给我的疼痛程序

Can any one please help me?

Bellow I am giving my Sore procedure

CREATE PROCEDURE SP_New_OrderNo @CustomerNo NVARCHAR(10), @ModelNo NVARCHAR(10), @ERROR VARCHAR(100)OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CusNo NVARCHAR(50)
DECLARE @MdlNo NVARCHAR(50)
SELECT @CusNo=CustomerNo, @MdlNo=ModelNo FROM TblSerialNo WHERE CustomerNo=@CustomerNo
IF @CusNo=@CustomerNo
BEGIN
IF @MdlNo=@ModelNo
BEGIN
SET @ERROR  = 'THIS CUSTOMER NO. AND MODEL NO. IS IN DATABASE PLEASE ENTER SERIAL NUMBER'
END
ELSE
BEGIN
SET @ERROR  = 'THIS CUSTOMER NO. IS IN DATABASE PLEASE ENTER MODELNO AND SERIAL NUMBER'
END
END
ELSE
BEGIN
SET @ERROR  = 'PLEASE ENTER CUSTOMER NO.'
INSERT INTO TblSerialNo (CustomerNo, ModelNo) VALUES (@CustomerNo, @ModelNo)
END

END

GO

EXEC SP_New_OrderNo '111','123'

感谢所有

Thanks To all

推荐答案

因为你还有额外的字段作为输出参数，而不是执行SP作为

Since you also have additional field as output parameter, instead of executing SP as

EXEC SP_New_OrderNo '111','123'

您需要执行以下操作：

you need to do something like:

declare @error varchar(100)
exec yourStoredProcedureName '111','123', @error output
print @error

我希望有一个空格顶部的定义：

I hope there is a space in the definition at the top:

@ERROR VARCHAR(100) OUTPUT

现在，由于您没有提供thrid参数，因此出现错误。即使它被标记为OUTPUT，它也被定义为SP的参数之一。所以，试试：

Now, error comes as you have not supplied the thrid parameter. Even though it is marked as OUTPUT, it''s defined as one of the parameters for SP. So, try:

EXEC SP_New_OrderNo '111','123', @ERROR OUTPUT
SELECT @ERROR

参考：

< a href =http://msdn.microsoft.com/en-us/library/ms378108.aspx> MSDN：使用带有输出参数的存储过程 [ ^ ]

存储过程：返回数据 [ ^ ]

使用查询窗口中的OUTPUT参数执行存储过程 [ ^ ]

Refer:
MSDN: Using a Stored Procedure with Output Parameters[^]
Stored Procedures: Returning Data[^]
Executing a stored procedure with OUTPUT parameters from Query window[^]

错误说明了一切！你需要提供一些东西来接收程序的out参数。



参见 http://sqlserverplanet.com/tsql/how-to-call-a-stored-procedure [ ^ ]

The error says it all! You need to give something to receive the out parameter of the procedure.

See http://sqlserverplanet.com/tsql/how-to-call-a-stored-procedure[^]

这篇关于sql server存储过程中的输出参数问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！