SQL Server 2008 stored procedure with multiple output parameters

Question

I have a stored procedure:

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 does not return anything. The purpose of this second output parameter is to retrieve the column that I just inserted into the database.

Answer 1

The execution of the procedure ends after the first RETURN , which "unconditionally completes the execution of the request or procedure."

 RETURN @order_ID 

Instead, consider returning both values ​​as a single set of records with

 SELECT @order_ID AS OrderID, @orderReferenceOutput AS OrderReference 

at the end of the procedure.

Answer 2

You have several output parameters, you should use them. RETURN values ​​for error / status codes, not 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 already in your OUTPUT parameters! END GO 

Answer 3

Remove RETURN from the 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.

Answer 4

There can only be one return per stored procedure. Since soo nas hits the first return, it exits proc.

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

Microsoft article on return

Answer 5

You can 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

Answer 6

You must put the output at the end of each output in the exec statement, otherwise everything you select is null. You can do a simple test to check this:

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