1

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

Ilyes
  • 14,640
  • 4
  • 29
  • 55
Joe
  • 4,143
  • 8
  • 37
  • 65
  • Try my answer below. – Dumi Dec 17 '18 at 06:39
  • 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

2 Answers2

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

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
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)

Joe
  • 4,143
  • 8
  • 37
  • 65
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