Dynamic tSQL Query from parameter

Question

I'm struggling with a dynamic query in tSQL to be used with Excel ODBC.

SQL Server 2016 via ODBC Driver 11 for SQL Server

I found this article but it only uses the table name stored in a parameter, I'd need the actual table to be within parameter: Simple dynamic TSQL query syntax

declare @t table(value1 int, value2 nvarchar(1024))
insert @t SELECT 1 as value1, 'test' as value2
declare @where nvarchar(max)
declare @query nvarchar(max)
declare @sql nvarchar(max)
set @where = ' WHERE value1 = 1'
set @query = 'Select * from @t'
set @sql = @query + @where
EXEC(@sql)

This results in the error message Must declare the table variable "@t"

Unfortunately I can't use temporary tables as the connector doesn't support temp tables.

My original query is far more complex and contains 6 different parameters all being injected at different points into the query and 2 table-parameters that hold temp results

Thanks in advance

Why does it have to be a table? It looks like you want to use some variables and be able to use them in JOIN clause. — Salman Arshad
– Salman Arshad, Commented Feb 9, 2018 at 15:10

Rigerta · Accepted Answer · 2018-02-09 14:55:05Z

1

You just need to declare your table as part of your query. The table is declared and recognized in that scope:

declare @t table(value1 int, value2 nvarchar(1024))
insert @t select 1 as value1, 'test' as value2
declare @where nvarchar(max)
declare @query nvarchar(max)
declare @sql nvarchar(max)
set @where = ' where value1 = 1'
set @query = 'declare @t table(value1 int, value2 nvarchar(1024)) 
              insert @t select 1 as value1, ''test'' as value2 
              select * from @t'
set @sql = @query + @where
exec(@sql)

Result:

answered Feb 9, 2018 at 14:55

Rigerta

4,0591 gold badge17 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

Dynamic tSQL Query from parameter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related