1

I have created a table variable and I am trying to reference the variable in my query string variable. The error is telling me to declare the variable when I think I already declared it. Can someone help me see where I am going wrong?

Error:

[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]
Must declare the scalar variable "@Temp2"

user2368085
  • 41
  • 1
  • 7
  • You are using a table variable `@Temp2` as if it was a `varchar` variable in `'... QueryId FROM ' + @Temp2 + 'WHERE Row_N ...'`. You cannot do that. Even if you could do that, it would make no sense because `@Temp2` *is* the name, it does not *contain* the name. And after you replace it with the proper `' ... QueryId FROM @Temp2 WHERE Row_Num = 1'`, it won't work because `@Temp2` is not visible from the batch that will execute the dynamic SQL. – GSerg Sep 13 '19 at 21:21
  • 2
    This should be an example on how to become a victim of SQL Injection. – Luis Cazares Sep 13 '19 at 21:28
  • Why are you using dynamic sql here in the first place? This code won't even come close to working. You have two columns in your table variable, but your insert has three columns. – Sean Lange Sep 13 '19 at 21:32

2 Answers2

0

The problem is + @Temp2 + in:

SET @Query = 'SELECT 
cq.Id,
(SELECT COUNT(*) AS CountRow FROM (' + (SELECT Query FROM tblCustomQuery WHERE Id = 154) + ') y) AS X FROM tblCustomQuery cq WHERE Id = IN (SELECT QueryId FROM ' + @Temp2 +  'WHERE Row_Num = 1)'

Change this bit to make @Temp2 inline:

SET @Query = 'SELECT 
cq.Id,
(SELECT COUNT(*) AS CountRow FROM (' + (SELECT Query FROM tblCustomQuery WHERE Id = 154) + ') y) AS X FROM tblCustomQuery cq WHERE Id = IN (SELECT QueryId FROM @Temp2 WHERE Row_Num = 1)'
Anoop R Desai
  • 712
  • 5
  • 18
  • 1
    There there will be an exception at `sp_executesql @Query` because `@Temp2` is [not visible](https://stackoverflow.com/q/4626292/11683) from the dynamic SQL batch. – GSerg Sep 13 '19 at 21:24
  • Good point, so would it probably be more helpful to not use `sp_executesql` and instead run the previous line directly, ie, `INSERT INTO @Temp` followed by the contents of `@Query`? – Anoop R Desai Sep 13 '19 at 21:29
  • @GSerg - I am using @Temp2 in the dynamic SQL batch using + @Temp2 + Am I only able to use it this way once? `SET @Query = 'SELECT cq.Id, (SELECT COUNT(*) AS CountRow FROM (' + (SELECT Query FROM tblCustomQuery WHERE Id IN (SELECT QueryId FROM @Temp2 WHERE Row_Num = @cnt + 1)) + ') y) AS X FROM tblCustomQuery cq WHERE Id = 153'` I want to replace with: `WHERE Id = 153'` with: `WHERE Id IN (SELECT QueryId FROM @Temp2 WHERE Row_Num = @cnt + 1)` Like I have in the first part of the query. Is that possible? – user2368085 Sep 13 '19 at 23:30
0

This duplicate link is give your answer.

MSDN QA link

You cannot use table variable in dynamic SQL. Beause the table variable is isolated from the scope of the dynamic SQL. You can only use temporary and normal tables.

Below two ways resolve.
1. You need to create temporary table and able to use in dynamic SQL.
2. You need to create table type and use in dynamic SQL.

JIKEN
  • 337
  • 2
  • 7