CODEWITHSUNDEEP
sql-serversql-server-2008
StevieB
StevieB

Reputation: 6541

SQL Server 2008 Stored Procedure with multiple output parameters

I have a stored procedure as follows:

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

For some reason the second output parameter @orderReferenceOutput returns nothing. The purpose of this second output parameter is to retrieve a column I have just inserted into the database.

Upvotes: 10

Views: 78540

Answers (6)

user3586922
user3586922

Reputation: 59

You must put output at the end of each output in the exec statement otherwise all you get out is null. You can do a simple test to verify this:

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

Upvotes: -2

RobertL
RobertL

Reputation: 49

Remove your RETURN from your original stored procedure.

Adjust your calling instruction to look like

DECLARE  @Id INT
DECLARE  @Reference VARCHAR(50)

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

@Id and @Reference are available in your calling process.

Upvotes: 4

Bill
Bill

Reputation: 23

It is possible to return more than one output parameter but as far as I know only with the Microsoft SQL Server JDBC Driver.

Using a Stored Procedure with Output Parameters

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

You have multiple output parameters, you should be using them. RETURN values are for error/status codes, not for data.

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

Upvotes: 19

David
David

Reputation: 1611

You can only have a single return in a stored procedure. As soo nas it hits the first return it's exiting the proc.

Use SET operations to assign values to your variables before issuing a RETURN.

Microsoft Article on Return

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

Procedure execution ends after your first RETURN which "Exits unconditionally from a query or procedure."

RETURN @order_ID

Instead, consider returning both values as one recordset with

SELECT @order_ID AS OrderID, @orderReferenceOutput AS OrderReference

at the end of the procedure.

Upvotes: 17

Related Questions