CODEWITHSUNDEEP
sqlsql-servert-sql
Joe
Joe

Reputation: 4183

TSQL: Using 'Output' clause in dynamic SQL

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

Upvotes: 2

Views: 1442

Answers (2)

Ilyes
Ilyes

Reputation: 14928

First of all, do not use sp_ prefix to your stored procedure, cause it reserved to System stored procedures by MS, and can lead to performance issue and other problems (as it can be a habit). Use SysName datatype for the table name, and use QUOTENAME() function when you concatenate the string.

You need to declare your table in the DynamicSQL as

CREATE PROCEDURE InsertPerson 
@Name varchar(50), 
@Table SysName
AS
  DECLARE @SQL NVARCHAR(MAX);

  SET @SQL = N'DECLARE @IDs TABLE (ID INT);'+
             'INSERT INTO ' + 
             QUOTENAME(@Table) + 
             ' (Name) OUTPUT INSERTED.ID INTO @IDs VALUES(@Name);'+
             'SELECT * FROM @IDs';
  EXECUTE sp_executesql @SQL,
                        N'@Name VARCHAR(50)',
                        @Name;

Demo

Upvotes: 4

Dumi
Dumi

Reputation: 1434

Try this;

  CREATE PROCEDURE sp_InsertPerson @Name varchar(50), @Table varchar(20) AS

  DECLARE @SQL nvarchar(200) = ''  
  SET @SQL = @SQL + 'DECLARE @InsertedId TABLE (Id int)';      
  SET @SQL = @SQL + 'INSERT INTO ' + @Table + ' (Name) OUTPUT INSERTED.Id INTO @InsertedId (Id) VALUES (''' + @Name + ''')'    
  SET @SQL = @SQL + 'SELECT Id FROM @InsertedId'
  IF (@Name is not null AND @Name != '')
    EXEC(@SQL)

Upvotes: 0

Related Questions