SQL Server 2008, getting column back as output parameter

I have the following stored procedure

ALTER PROCEDURE [dbo].[sp_web_orders_insert]
(
    @userId int = def开发者_开发百科ault,
    @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);

    SET @order_ID = @@IDENTITY

    SET @orderReferenceOutput = select top 1 orderReference from web_orders

But getting syntax issue near select, how can I get back the value for orderReference which I have just inserted ?

Two points:

• use SCOPE_IDENTITY() instead of @@IDENTITY (see why this is here or Google for it...)
• use a SELECT statement to get your value back

Something like:

SET @order_ID = SCOPE_IDENTITY()

SELECT 
    @orderReferenceOutput = SELECT orderReference  
                            FROM dbo.web_orders
                            WHERE order_Id = @order_ID

Try the Following code part, hope it will help you

SET @orderReferenceOutput = select @orderReference=(top 1 orderReference) from web_orders

select @order_ID = order_ID, @orderReferenceOutput = orderReference  
  from dbo.web_orders
  where order_ID = SCOPED_IDENTITIY();

Or get rid of output parameters and return inserted record directly:

select * from dbo.web_orders where order_Id = SCOPED_IDENTITIY();

Yes. Procedures can return data, just like queries.