CODEWITHSUNDEEP
sqlsql-serverstored-procedures
WiiMaxx
WiiMaxx

Reputation: 5420

SQL Insert into … ( SELECT *, SCOPE_IDENTITY() FROM … )

I created a stored procedure that should do 2 inserts in one

First step I want to create a new entry per insert into.

Second step I will catch the created Id from this entry

Third step I want to copie multiple entries per select from one table and insert those to the same table with the Id

Create PROCEDURE dbo.Rolle_Copie
    @Id as int,
    @newId as int = 0,
    @Name AS nvarchar(50)
AS
    INSERT INTO Rollen (RolleName) 
    VALUES (@Name)

    @newId = SCOPE_IDENTITY()

    INSERT INTO Berechtigung_Rolle (RefRolleId,RefBerechtigungId)
        SELECT   
           RefBerechtigungId, @newId 
        FROM     
           Berechtigung_Rolle 
        WHERE    
           RefRolleId = @Id

    RETURN

but I get an error

Wrong syntax next to @newId

Could somebody please enlight me what's wrong?

Any advice is greatly appreciated

Upvotes: 3

Views: 12070

Answers (1)

John Woo
John Woo

Reputation: 263683

don't forget to use SET

SET @newId = SCOPE_IDENTITY()

Upvotes: 7

Related Questions