CODEWITHSUNDEEP
sqlsql-serverstored-procedures
StealthRT
StealthRT

Reputation: 10542

SQL Server stored proceedure getting inserted ID and using it in the next query

I am trying to pull the inserted ID from the first query and use it in my second query in order to update another table with that returned ID.

DECLARE @SQLQuery VARCHAR(MAX);
SET @SQLQuery = 'DECLARE @ID VARCHAR(50); 
                 SET @ID = INSERT INTO 
                    NEW_Project (@val1) 
                 OUTPUT Inserted.ID 
                    VALUES (@val2);
                 UPDATE 
                    mLine 
                 SET 
                    projectID = @ID 
                 WHERE 
                    employeeID = @val3'
EXEC [dbo].[_chkQ] @SQLQuery

Currently I am getting this error when trying to do the above query:

Incorrect syntax near the keyword 'INSERT'.

How should I go about doing this in the correct syntax?

Upvotes: 1

Views: 46

Answers (2)

Camden Reslink
Camden Reslink

Reputation: 93

You need to output to a table variable, and then extract the ID from there:

DECLARE @SQLQuery VARCHAR(MAX);
SET @SQLQuery = 'DECLARE @ID_Table table (ID VARCHAR(50)); 
                 INSERT INTO 
                    NEW_Project (@val1) 
                 OUTPUT Inserted.ID INTO @ID_Table
                    VALUES (@val2);
                 DECLARE @ID VARCHAR(50) = (SELECT ID FROM @ID_Table );
                 UPDATE 
                    mLine 
                 SET 
                    projectID = @ID 
                 WHERE 
                    employeeID = @val3'
EXEC [dbo].[_chkQ] @SQLQuery

Upvotes: 1

user1443098
user1443098

Reputation: 7635

DECLARE @SQLQuery VARCHAR(MAX);
SET @SQLQuery = 'DECLARE @ID numeric(38,0); 
                 INSERT INTO 
                    NEW_Project (@val1) 
                 OUTPUT Inserted.ID 
                    VALUES (@val2);
                 SET @ID = SCOPE_IDENTITY();
                 UPDATE 
                    mLine 
                 SET 
                    projectID = @ID 
                 WHERE 
                    employeeID = @val3'

EXEC [dbo].[_chkQ] @SQLQuery;

Upvotes: 2

Related Questions