TSQL: Using 'Output' clause in dynamic SQL

Question

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

Your path leads to doom. Go back before it's too late. SQL is a declarative language. So your code should follow the paradigm enforced by the engine and language. It is unlikely (and logically invalid) for a "person" to exist in different tables as this means your schema is not properly normalized. I suggest you completely rethink your schema and how you intend to approach interfacing with it from your application. — SMor, Dec 17 '18 at 14:06

Ilyes · Accepted Answer · 2018-12-17T21:03:07.153

3

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

edited Dec 17 '18 at 21:03

answered Dec 17 '18 at 07:19

Ilyes

14,640
4
29
55

The `DECLARE @IDs TABLE(ID INT);` after `AS` and `= @Name` at the end are not needed. Edit your answer and I'll mark it as the answer. Please let me know if there is a reason you included them. – Joe Dec 17 '18 at 16:50
@Joe The last line `@Name = @Name;` is needed if you going to pass the Name as a parameter instead of concatenate it as `VALUES('''+@Name+''');'` – Ilyes Dec 17 '18 at 19:32
I edited your db<>fiddle to remove ` = @Name` and it works fine (`EXEC dbo.InsertPerson 'B', 'T';`) – Joe Dec 17 '18 at 20:50
@Joe Since the param of the SP is the same it will work, but if you have another name it won't work, so for this you can remove it – Ilyes Dec 17 '18 at 20:54

score 0 · Answer 2 · edited Dec 17 '18 at 07:11

0

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)

edited Dec 17 '18 at 07:11

Joe

4,143
8
37
65

answered Dec 17 '18 at 06:24

Dumi

1,414
4
21
41

I edited your answer to add single quotes, you need two sets of three single quotes. `VALUES (''' + @Name + ''')'`. It's all about the scope huh. So why are the scalar variables in scope but not the table variable? – Joe Dec 17 '18 at 07:11

TSQL: Using 'Output' clause in dynamic SQL

2 Answers2