SQL Server 2008 存储过程的多个输出参数

Question

我有一个存储过程如下：

ALTER PROCEDURE [dbo].[sp_web_orders_insert]
(
    @userId int = default,
    @custId int = default,
    @orderDate datetime = default,
    @orderTotal money = default,
    @statusId int = default,
    @orderReference varchar(50) = default,
    @custReference varchar(50) = default,
    @order_ID INT output,
    @orderReferenceOutput varchar(50) output
)
AS


    SET NOCOUNT OFF;
    INSERT INTO [web_orders] ([user_ID], [cust_ID], [orderDate], [orderTotal], [statusId], [orderReference], [custReference]) VALUES (@userId, @custId, @orderDate, @orderTotal, @statusId , 'PLC' + REPLICATE('0', (7 - LEN((select MAX(order_ID) from web_orders)))) +  CAST((select(max(order_ID)+1) from web_orders) AS VARCHAR(5)), @custReference);

    SELECT @order_ID = @@IDENTITY
    RETURN @order_ID

    SELECT @orderReferenceOutput = 'PLC' + REPLICATE('0', (7 - LEN((select MAX(order_ID) from web_orders)))) +  CAST((select(max(order_ID)+1) from web_orders) AS VARCHAR(5))
    RETURN @orderReferenceOutput 

由于某些原因，第二个输出参数@orderReferenceOutput没有返回任何内容。第二个输出参数的目的是检索我刚刚插入到数据库中的一列。

- StevieB

---

最简单的解决方法是注释掉你的"RETURN"命令。这不是它们的作用。 - Aaron Bertrand

Answer 1

你有多个输出参数，应该使用它们。RETURN值用于错误/状态代码，而不是数据。

ALTER PROCEDURE [dbo].[sp_web_orders_insert]
    @userId ...,
    @order_ID INT OUTPUT,
    @orderReferenceOutput VARCHAR(50) OUTPUT
AS
BEGIN
    SET NOCOUNT OFF; -- WHY?????????

    INSERT INTO [web_orders] (user_ID, ...) SELECT @userId, ...;

    SELECT @order_ID = SCOPE_IDENTITY(); -- preferred over @@IDENTITY;

    -- using @order_ID here instead of SELECT MAX() twice:

    SELECT @orderReferenceOutput = 'PLC' 
        + REPLICATE('0', (7 - LEN((@order_ID+1))))
        + CAST((@order_ID+1) AS VARCHAR(5)) -- (5)? This breaks when you hit order #100,000

    RETURN; -- do not RETURN any data - it's already in your OUTPUT parameters!
END
GO

- Aaron Bertrand

Answer 2

当你执行第一个RETURN语句时，过程的执行将结束，该语句"无条件地从查询或过程中退出。"

RETURN @order_ID

相反，考虑将这两个值作为一个记录集返回

SELECT @order_ID AS OrderID, @orderReferenceOutput AS OrderReference

在过程的末尾。

- Joe Stefanelli

---

太好了，谢谢你。当系统允许时，我会接受你的答案。 - StevieB

@Joe，他已经有了输出参数，为什么不使用它们，而要增加记录集的开销呢？ - Aaron Bertrand

Answer 3

将原存储过程中的RETURN移除。

调整调用指令，使其看起来像：

DECLARE  @Id INT
DECLARE  @Reference VARCHAR(50)

EXEC [dbo].[sp_web_orders_insert],  @order_ID = @Id OUTPUT,
  @orderReferenceOutput = @Reference OUTPUT

@Id和@Reference可以在您的调用过程中使用。

- RobertL

Answer 4

存储过程只能有一个返回值。一旦遇到第一个返回语句，它就会退出存储过程。

在发出 RETURN 之前，请使用 SET 操作将值分配给变量。

Microsoft 对于 RETURN 的文章

- David

Answer 5

可以返回多个输出参数，但据我所知，只有使用 Microsoft SQL Server JDBC 驱动程序才能实现。

使用带有输出参数的存储过程

- Bill

Answer 6

您必须在exec语句的末尾放置输出，否则你将得到空值。你可以进行简单的测试来验证这一点：

if(@order_ID is null)
 Print '@order_ID is null'
else
 Print '@order_ID is not null'

- user3586922

---

我认为这并没有回答楼主的问题。 - matsjoyce