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

I have created a stored procedure that should do 2 inserts into one

First step: I want to create a new record for every insert.

Second step I will catch the generated Id from this post

Step three. I want to copy multiple records to choose from from one table and paste them into the same table with 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 the error

Incorrect syntax near @newId

Can someone please enlighten me what is wrong?

Any advice would be greatly appreciated

+3


source to share


1 answer


don't forget to use SET



SET @newId = SCOPE_IDENTITY()

      

+7


source