运行此代码时:
DECLARE @strSelectQuery NVARCHAR(MAX),
@strColumnList NVARCHAR(MAX),
@strTempTable NVARCHAR(50),
@strMainTable NVARCHAR(50)
SET @strTempTable = 'TempdimRevenueCode'
SET @strMainTable = 'dimRevenueCode'
SET @strColumnList = 'DBID,Revenue_Code_ID,Revenue_Code_AltID,revenue_code,revenue_code_description,BeginUTCDateTime,EndUTCDateTime,IsCurrent'
SELECT @strSelectQuery = COALESCE(@strSelectQuery,'UNION ALL') + ' UNION ALL ' + CHAR(13)
+ ' SELECT '+CONVERT(VARCHAR,DBID) +' as ' + @strColumnList + CHAR(13)
+ ' FROM ['+ InstanceName +'].['+DBName+'].dbo.'+@strMainTable + CHAR(13)
+ ' WHERE BeginUTCDateTime > ' + CHAR(13)
+' (SELECT ISNULL( MAX(BeginUTCDateTime) ,''1900-01-01'') FROM '+@strMainTable + ' WHERE DBID='+CONVERT(VARCHAR,DBID)+')'+CHAR(13)
+' OR EndUTCDateTime > ' + CHAR(13)
+' (SELECT ISNULL( MAX(BeginUTCDateTime) ,''1900-01-01'') FROM '+@strMainTable + ' WHERE DBID='+CONVERT(VARCHAR,DBID)+')'+CHAR(13)
FROM adminODSDBDetails
WHERE Active_TF = 1 and ClientProductName='GEMINI'
SELECT @strSelectQuery
EXECUTE sp_executesql @strSelectQuery
选择动态语句(select @strSelectQuery)
时,它显示为NULL
。
如果我从动态代码中删除ClientProductName='GEMINI'
并选择@strSelectQuery
,它将打印有效的 SQL 语句。
这可能是由两个因素引起的:
-
where
中的任一附加条件都会导致查询不返回任何结果(没有记录满足该条件的所有条件( -
或者它返回记录,但该记录中的一个值是
null
,这使得整个语句的计算值为null
所以我建议单独运行查询:
SELECT DBID,InstanceName,DBName
FROM adminODSDBDetails
WHERE Active_TF = 1 and ClientProductName='GEMINI'
无需将其分配给变量,也没有额外的文本,看看原因是什么。