0

I want to use a table in a dynamic query. But columns of table are not fixed and they may change.

declare @t table(ID int, Fname varchar(50), Lname varchar(50)); -- Columns may change
insert into @t(ID, Fname, Lname)
values
(1, 'Jack', 'Martinez'),
(2, 'Alex', 'Desoza');

I have tried below code:

declare @tablename nvarchar(max) = '@t'
declare @query nvarchar(max) = 'select * from '+ @tablename 
exec sp_executesql @query

But the only thing I get is :

Must declare the scalar variable "@t".

Behnam
  • 1,039
  • 2
  • 14
  • 39
  • Does this answer your question? [Pass a TABLE variable to sp\_executesql](https://stackoverflow.com/questions/4258798/pass-a-table-variable-to-sp-executesql) – Serg Nov 23 '20 at 09:23

1 Answers1

1

You need to bring the definition of your table variable into 'dynamic' part of the code:

declare @query nvarchar(max) = '
declare @t table(ID int, Fname varchar(50), Lname varchar(50));
insert into @t(ID, Fname, Lname)
values
(1, ''Jack'', ''Martinez''),
(2, ''Alex'', ''Desoza'');
select * from @t' 
exec sp_executesql @query
Pavel Nefyodov
  • 876
  • 2
  • 11
  • 29
  • This poses the question of why use a dynamic statement at all though, when the statement is no longer dynamic. – Thom A Nov 23 '20 at 10:39
  • It's a good point @Larnu. The list of the columns in table variable declaration as well as values in INSERT column can be populated dynamically. – Pavel Nefyodov Nov 23 '20 at 11:17