What is wrong with the syntax of this OUTPUT statement (SQL Server 2005)?

Question

I'm trying to use the OUTPUT statement in a stored procedure in order to return the ID of a newly inserted row. The stored procedure is:

CREATE PROCEDURE PROC_RESTORE_REQUEST_TO_QUEUE
    @cs_uri_stem varchar(900),
    @cs_uri_query varchar(2500),
    @date datetime,
    @time datetime,
    @queue_state smallint,
    @process_id int,
    @simulation_start_time bigint,
    @num_failures smallint

AS

SET NOCOUNT ON

INSERT INTO [DD#WORK].[dbo].[ebhFifoQueue] ([cs-uri-stem],[cs-uri-query],[date],[time],[queue_state],[process_id],[simulation_start_time],[num_failures]) 
VALUES (@cs_uri_stem,@cs_uri_query,@date,@time,@queue_state,@process_id,@simulation_start_time,@num_failures) 

OUTPUT INSERTED.id

When I try to compile this stored procedure, I get an error message:

Incorrect syntax near 'OUTPUT'.

---

I've tried several permutations of this code to no avail (same error message), including moving the OUTPUT statement onto the same line as the INSERT statement. Do you know what the problem is with my syntax? Thanks in advance for your help,

-Eric

Answer 1

Its the order. The OUTPUT clause should go between the INSERT and the VALUES lines.

Just move yours, like this:

INSERT INTO [DD#WORK].[dbo].[ebhFifoQueue] ([cs-uri-stem],[cs-uri-query],[date],[time],[queue_state],[process_id],[simulation_start_time],[num_failures]) 
OUTPUT INSERTED.id
VALUES (@cs_uri_stem,@cs_uri_query,@date,@time,@queue_state,@process_id,@simulation_start_time,@num_failures) 

Answer 2

I think it should be like:

INSERT INTO [DD#WORK].[dbo].[ebhFifoQueue] ([cs-uri-stem],[cs-uri-query],[date],[time],[queue_state],[process_id],[simulation_start_time],[num_failures])

OUTPUT INSERTED.id 

VALUES 
(@cs_uri_stem,@cs_uri_query,@date,@time,@queue_state,@process_id,@simulation_start_time,@num_failures)

You can also add a "INTO @MyVariable" or "INTO MyTable" after the OUPUT statement