正如@brad所指出的,这里的问题是临时表#dbNames包含空值。下面是更新后的SQL,它不仅允许我接收我正在寻找的输出,还允许我从不同SQL实例上的不同数据库创建一个没有空值的临时表,以传递到游标中。也许有“更好”的方法,但这解决了我的问题。
SQL:
/* start */
begin try
DROP TABLE #SaaS
DROP TABLE #dbNames
DROP TABLE #dbNamesCol1
DROP TABLE #dbNamesCol2
DROP TABLE #dbNamesALL
end try
begin catch
end catch
DECLARE
@prefix nvarchar(50) = 'WFA'
/**
* Create 5 temp tables for processing
*/
CREATE TABLE #dbNames (dbName NVARCHAR(255) --will hold the initial database names.
, dbName2 NVARCHAR(255))
CREATE TABLE #dbNamesCol1 (db NVARCHAR(255) --holding table
, dbName NVARCHAR(255)) --will remove null values and be the fetch column for @dbName
CREATE TABLE #dbNamesCol2 (db NVARCHAR(255) --holding table
, dbName2 NVARCHAR(255))--will remove null values and be the fetch column for @dbName2
CREATE TABLE #dbNamesAll (db NVARCHAR(255) --Final table for dbName and dbName2 without NULL values
, dbName NVARCHAR(255) --will join on db
, dbName2 NVARCHAR(255)) --Cursor will fetch from this final table
CREATE TABLE #SaaS (dbName NVARCHAR(255) -- Output Table
, varA NVARCHAR(255)
, varB NVARCHAR(255))
/**
* Get the databases - all for now
*/
INSERT INTO #dbNames(dbName, dbName2)
SELECT CASE WHEN [name] LIKE '%Reporting' THEN [name] END as dbName
, CASE WHEN [name] LIKE '%System' THEN [name] END as dbName2
FROM SYS.DATABASES
WHERE State = 0
/**
* Create TEMP Table that contains db names from two different
Databases. #dbNamesAll will be a join on db from
#dbNamesCol1 and #dbNamesCol2 with no null values.
*/
INSERT INTO #dbNamesCol1 (db, dbName)
select REPLACE(dbName,'_Reporting',''), dbName
from #dbNames
WHERE dbName IS NOT NULL
INSERT INTO #dbNamesCol2 (db, dbName2)
select REPLACE(dbName2,'_System',''), dbName2
from #dbNames
WHERE dbName2 IS NOT NULL
INSERT INTO #dbNamesALL (db, dbName, dbName2)
select a.db,
a.dbName,
b.dbName2
from #dbNamesCol1 a
LEFT JOIN #dbNamesCol2 b ON b.db = a.db
/**
* A few variables for the Database list cursor
*/
DECLARE @dbName NVARCHAR(255)
, @dbName2 NVARCHAR(255)
, @sql NVARCHAR(MAX)
/**
* get the values, only from ACA instances
*/
DECLARE EachDatabase CURSOR FOR
SELECT [dbName],
[dbName2]
FROM #dbNamesALL
OPEN EachDatabase
FETCH NEXT FROM EachDatabase INTO @dbName, @dbName2
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @sql = '
DECLARE @varA AS nvarchar(255), @varB AS NVARCHAR(255)
SET @varB = (
SELECT customerName
FROM ' + @dbName2 + '.User.SystemSettings
)
SET @varA = (
select top 1 varA
from
' + @dbName + '.eUser.tablecol
WHERE varA >= 1/1/2018
)
--REPORT STATUS
insert into #SaaS ( DBName
, varA
, varB
)
select ''' + REPLACE(@dbName,'Reporting','') + '''
, @varA
, @varB
'
exec(@sql)
FETCH NEXT FROM EachDatabase INTO @dbName, @dbName2
END
CLOSE EachDatabase
DEALLOCATE EachDatabase
-----------------------------------------End Table for SaaS
SELECT *
from #SaaS
order by dbName
/* end */
