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
– SMor, Commented Dec 17, 2018 at 14:06

Ilyes · Accepted Answer · 2018-12-17 21:03:07Z

4

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, 2018 at 21:03

answered Dec 17, 2018 at 7:19

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Joe Over a year ago

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.

Ilyes Over a year ago

@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+''');'

Joe Over a year ago

I edited your db<>fiddle to remove ` = @Name` and it works fine (EXEC dbo.InsertPerson 'B', 'T';)

Ilyes Over a year ago

@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

Joe · Accepted Answer · 2018-12-17 07:11:20Z

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, 2018 at 7:11

Joe

4,2139 gold badges41 silver badges68 bronze badges

answered Dec 17, 2018 at 6:24

Dumi

1,4544 gold badges22 silver badges48 bronze badges

1 Comment

Joe Over a year ago

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?

Collectives™ on Stack Overflow

TSQL: Using 'Output' clause in dynamic SQL

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related