I'm using an Output
clause in my Insert
statement which requires use of a Table Variable
. I also want the Table name
to be dynamic so I'm using dynamic SQL
but it won't allow use of a Table Variable
. I get the error Must declare the scalar variable "@InsertedId"
.
CREATE PROCEDURE sp_InsertPerson @Name varchar(50), @Table varchar(20) AS
DECLARE @InsertedId TABLE (Id int)
DECLARE @SQL nvarchar(200) = 'INSERT INTO ' + @Table + ' (Name) OUTPUT INSERTED.Id INTO ' + @InsertedId + ' VALUES (' + @Name + ')'
IF (@Name is not null AND @Name != '')
EXEC(@SQL)
SELECT Id FROM @InsertedId
How can I both use the Output
clause and a dynamic Table name