David David - 6 months ago 9
SQL Question

Running a query across multiple databases

I recently asked a question about making a query that would document the results of a test query in a separate table.

There was, however, one thing that I didn't ask in the previous question that seemed like it was a large enough matter to simply make a new topic about. This query (courtesy of Steve Mangiameli):

DECLARE @testStatus NVARCHAR(MAX);

IF (
SELECT COUNT(*)
FROM Table1
WHERE Table1.Column1 = ''
) = 0
SET @testStatus = 'Test Passed'
ELSE
SET @testStatus = 'Test Failed'

INSERT INTO Table2 (FileName, Date, Result)
VALUES ('File1', GetDate(), @testStatus)


needs to be run across multiple databases. Here is the current version that I have worked up using another query of mine that does a similar thing but this one doesn't work. EDIT: To be more clear, I get an error message `Must declare the scalar variable "@testStatus".

DECLARE @dbname NVARCHAR(200);
DECLARE @testStatus NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name LIKE '%DBTag%'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @query = CAST('
IF (
SELECT COUNT(*)
FROM [' +@dbname+ '].dbo.Table1
WHERE [' +@dbname+ '].dbo.Table1.Column1 = ''''
) = 0
SET @testStatus = ''Test Passed''
ELSE
SET @testStatus = ''Test Failed''

INSERT INTO [Database].dbo.Table2(FileName, Result, Date)
VALUES (''File1'', @testStatus, GETDATE())'
AS NVARCHAR(MAX))

EXECUTE (@query)

FETCH NEXT FROM db_cursor INTO @dbname

END
CLOSE db_cursor
DEALLOCATE db_cursor;


I'm wondering if there is something fundamentally "wrong" with how I've even gone about trying to make this work.

Answer

The variable is declared outside of your dynamic sql. When the dynamic sql executes your variables are out of scope. You can fix this easily by declaring the variable again inside your dynamic sql.

SET @query = CAST('
declare @testStatus varchar(20);
IF (
    SELECT COUNT(*)
    FROM [' +@dbname+ '].dbo.Table1
    WHERE [' +@dbname+ '].dbo.Table1.Column1  = ''''
) = 0
    SET @testStatus = ''Test Passed''
ELSE
    SET @testStatus = ''Test Failed''

INSERT INTO [Database].dbo.Table2(FileName, Result, Date)
VALUES (''File1'', @testStatus, GETDATE())'
AS NVARCHAR(MAX))