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