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

From MSDN

DECLARE @MyTableVar table( NewScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);

INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

Answer 2

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 3

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

Answer 4

Should be something like:

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,
    @new_id int OUTPUT

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)  

SET @new_id = SCOPE_IDENTITY()