title: SQL Server 2008 Stored Procedure with multiple output parameters
body: <p>I have a stored procedure as follows:</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>For some reason the second output parameter <code>@orderReferenceOutput</code> returns nothing.  The purpose of this second output parameter is to retrieve a column I have just inserted into the database.</p>