What is wrong with the syntax of this OUTPUT statement (SQL Server 2005)?
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#WOR开发者_Python百科K].[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
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)
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
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());
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()
精彩评论