Table variable in dynamic query

Question

Table variable in dynamic query

Naina_11

SSC Eights!

Points: 921
More actions
July 2, 2015 at 5:18 am

#309547

hey i have started working with dynamic queries recently. I am using a table variable and need to add a join in query dynamically.
For Eg- @TableVariable
SET @query_from = @query_from + CHAR(10) + ' JOIN @TableVariable on ABC.ID = @TableVariable.ID '
BUt it gives an error that @TableVariable must be declared
PLEASE IF ANYONE CAN HELP ASAP

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Table variables are only visible in the scope where they are defined. They're like normal variables. Since you declared the table variable outside the dynamic SQL, it's not visible inside the dynamic SQL.

Try using a temp table instead, their scoping rules are different and a temp table declared outside the dynamic SQL is visible inside.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Y.B. SSChampion Points: 11643 More actions · Answer 2

It becomes much simpler to visualize what you are doing wrong when you output your string.

DECLARE @TableVariable NVARCHAR(50) = 'MyTable'

DECLARE @query_from NVARCHAR(MAX) = ''

---You are doing this

SET @query_from = @query_from + CHAR(10) + ' JOIN @TableVariable on ABC.ID = @TableVariable.ID '

SELECT @query_from

SET @query_from = ''

--When you want to do this

SET @query_from = @query_from + CHAR(10) + ' JOIN ' + @TableVariable + ' on ABC.ID = ' + @TableVariable + '.ID '

SELECT @query_from

SELECT quote FROM brain WHERE original = 1
0 rows returned

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

yb751 (7/2/2015)
--When you want to do this
SET @query_from = @query_from + CHAR(10) + ' JOIN ' + @TableVariable + ' on ABC.ID = ' + @TableVariable + '.ID '

That will only work if @TableVariable is a string, not a table variable. The OP says he has a table variable, not a variable with the name of a table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Y.B. SSChampion Points: 11643 More actions · Answer 4

GilaMonster (7/2/2015)
yb751 (7/2/2015)
--When you want to do this
SET @query_from = @query_from + CHAR(10) + ' JOIN ' + @TableVariable + ' on ABC.ID = ' + @TableVariable + '.ID '
That will only work if @TableVariable is a string, not a table variable. The OP says he has a table variable, not a variable with the name of a table.

Right you are...why is it so easy to miss the obvious. :crazy:

SELECT quote FROM brain WHERE original = 1
0 rows returned

Y.B. SSChampion Points: 11643 More actions · Answer 5

To illustrate Gila's point I used AdventureWorks to demonstrate how a temp table could work.

BAD

DECLARE @query_from NVARCHAR(MAX) = ''

DECLARE @query NVARCHAR(MAX)

DECLARE @TableVariable TABLE (ID INT)

INSERT INTO @TableVariable VALUES (1),(2)

SET @query_from = CHAR(10) + ' JOIN @TableVariable on Sales.Customer.CustomerID = @TableVariable.ID '

SET @query = 'SELECT * FROM Sales.Customer ' + @query_from

EXECUTE sp_executesql @query

GOOD

DECLARE @query_from NVARCHAR(MAX) = ''

DECLARE @query NVARCHAR(MAX)

CREATE TABLE #TableVariable (ID INT)

INSERT INTO #TableVariable VALUES (1),(2)

SET @query_from = @query_from + CHAR(10) + ' JOIN #TableVariable on Sales.Customer.CustomerID = #TableVariable.ID '

SET @query = 'SELECT * FROM Sales.Customer ' + @query_from

EXECUTE sp_executesql @query

SELECT quote FROM brain WHERE original = 1
0 rows returned

Naina_11 SSC Eights! Points: 921 More actions · Answer 6

Thanks.. I also used the temp table instead. BUt my lead somehow asked me to use table variable 🙁

So now ill be using temp tables only

Naina_11 SSC Eights! Points: 921 More actions · Answer 7

thanks Gila .... 🙂 my lead asked me to use table variable instead so it was confusing...

well i have used temp tables now

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

nidhi.naina (7/2/2015)
BUt my lead somehow asked me to use table variable 🙁

Ask him why, given the known performance problems that table variables often cause.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass