"Must declare the table variable" in while loop-CodePudding

Im writing a script to perform a comparison between a base table, and all databases in my server.

My approach for this case is using table variables.

Performig the query harcoded, returns the expected results, but the issue comes when trying to perform a dynamic query for each database.

DECLARE @myTableVariableBase TABLE (Version varchar(10),count int, dbname Varchar(50))
DECLARE @dbname VARCHAR(200)

DECLARE db_cursor CURSOR FOR 
SELECT name FROM sys.databases
WHERE name NOT IN ('databases_not_used')

INSERT INTO @myTableVariableBase SELECT Version, count(Version),'Base_Database' FROM [Base_Database].[dbo].[table] GROUP BY Version ORDER BY Version desc

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname  

WHILE @@FETCH_STATUS = 0  
BEGIN  


DECLARE @myTableVariableTarget TABLE (Version varchar(10),count int, dbname Varchar(50))


EXEC('INSERT INTO @myTableVariableTarget SELECT Version, count(Version), @dbname FROM '   @dbname   '.[dbo].[Table]')

PRINT @dbname

SELECT * FROM @myTableVariableBase
EXCEPT
SELECT * FROM @myTableVariableTarget


FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor  
DEALLOCATE db_cursor

This query returns the message

Msg 1087, Level 15, State 2, Line 1 Must declare the table variable "@myTableVariableTarget". Msg 137, Level 15, State 2, Line 1 Must declare the scalar variable "@dbname".

Do you have any suggestions?

Thanks in advance!

CodePudding user response：

Table variables have current batch scope. Use a Temporary Table instead, which is visible from nested scopes, like your dynamic query.

Instead of

DECLARE @myTableVariableTarget TABLE (Version varchar(10),count int, dbname Varchar(50))

create table #myTableVariableTarget  (Version varchar(10),count int, dbname Varchar(50))

CodePudding user response：

As mentioned, you cannot use table variables from a different scope.

But you don't need to. You can just creat one big script that does the whole thing, then execute it.

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(CAST('
SELECT Version, count(Version), ''Base_Database''
FROM [Base_Database].[dbo].[table]
GROUP BY Version

EXCEPT

SELECT Version, count(Version), '   QUOTENAME(d.name, '''')   '
FROM '   QUOTENAME(d.name)   '.[dbo].[Table];
'
  AS nvarchar(max)), '
'  )
FROM sys.databases d
WHERE d.name NOT IN ('databases_not_used');

EXEC sp_executesql @sql;