在 T-SQL 的情况下无效的假结果表达式

Question

我正在编写一个对表进行透视的查询。问题是，根据映射文件，我想将值输入为常量或变量。

例如 - 如果在映射文件中 ExtDate 是常量，例如 ExtDate = '2017-12-31' 我想使用此值 (2017-12-31)。但是，如果 ExtDate 以"Var"开头，我想使用变量值 - 例如，当 ExtDate = VarOpenDate 时，那么我想用 OpenDate 列中的值来完成该列。下面是映射文件中的示例行：

CREATE TABLE MappingFile (ColNum INT, Variable CHAR(50), IsUsed CHAR(50), ID CHAR(50), ExtDate CHAR(50), DataDate CHAR(50), ValueDate CHAR(50), Flag CHAR(50), Unit CHAR(50))
INSERT INTO MappingFile VALUES (1,  'ClientId', 'YES', 'VarAcctID', '2017-12-31', 'VarSigningDate', 'VarSigningDate', 'X', '')  
INSERT INTO MappingFile VALUES (2,  'ProductGroup', 'YES', 'VarAcctID', 'VarOpenDate', 'VarSigningDate', 'VarSigningDate', 'X', '')     
INSERT INTO MappingFile VALUES (3,  'ProductType', 'YES', 'VarAcctID',  'VarOpenDate', 'VarSigningDate', 'VarSigningDate', 'X', '')     

为了做到这一点，我在下面写了一个代码(这是一个简化，因为有更多的列，整个查询都在插入while循环中)。

DECLARE @I INT = 2
DECLARE @COL CHAR(50)
DECLARE @ID CHAR(50)
DECLARE @EDT CHAR(50)
DECLARE @DDT CHAR(50)
DECLARE @DTS CHAR(50) = 'dataset_name'
SET @ID  = (SELECT ID        FROM MappingFile WHERE ColNum = @I)
SET @EDT = (SELECT ExtDate   FROM MappingFile WHERE ColNum = @I)
SET @DDT = (SELECT DataDate  FROM MappingFile WHERE ColNum = @I)
SET @COL = (SELECT Variable  FROM MappingFile WHERE ColNum = @I)
EXEC('
SELECT ''' + @DTS + ''',
CASE WHEN SUBSTRING(''' + @ID  + ''', 1, 3) = ''Var'' THEN ' + @ID  + ' ELSE ''' + @ID  + ''' END,
CASE WHEN SUBSTRING(''' + @EDT + ''', 1, 3) = ''Var'' THEN ' + @EDT + ' ELSE ''' + @EDT + ''' END,
CASE WHEN SUBSTRING(''' + @DDT + ''', 1, 3) = ''Var'' THEN ' + @DDT + ' ELSE ''' + @DDT + ''' END,
''' + @COL + ''',
' + @COL + '        
FROM ' + @DTS + '
WHERE ' + @COL + ' IS NOT NULL
')

不幸的是，当 ExtDate 是常量字符串时，查询会给我一个错误。这是由以下事实引起的：

THEN ' + @EDT + '

返回不是列名称的字符串。这给了我一个错误，虽然它不应该，因为如果

SUBSTRING(''' + @ID  + ''', 1, 3) <> ''Var''

那么案件的结果是

' ELSE ''' + @DDT + '''

它不是列名，而是常量字符串。

Answer 1

您已经动态构建了一个无法解析的查询。

请考虑以下测试查询：

DECLARE @T TABLE (
Col int
);
INSERT INTO @T VALUES (1);
SELECT CASE 
WHEN 1=0 THEN NoSuch ELSE 'Hi' END
FROM @T;

运行这个，它会导致

Msg 207，级别 16，状态 1，第 8 行 无效的列名称"NoSuch"。

因为即使 CASE 不执行THEN并跳到ELSE，解析器也不会事先知道这一点并检查以确保NoSuch是一列。 如果不是，它会在尝试运行查询之前拒绝查询。

一种解决方案是在动态字符串之外检查变量。

Answer 2

感谢@Tab Alleman，我设法找到了解决方案。关于解析器的现象是完全正确的，因此我不得不检查 EXEC 命令之外的变量。

下面找到有效的代码：

DECLARE @I INT = 2
DECLARE @COL CHAR(50)
DECLARE @ID CHAR(50)
DECLARE @EDT CHAR(50)
DECLARE @DDT CHAR(50)
DECLARE @DTS CHAR(50) = 'dataset_name'
SET @ID  = (SELECT CASE WHEN SUBSTRING(ID       , 1, 3) = 'Var' THEN LTRIM(RTRIM(RIGHT(ID       , LEN(ID        ) - 3))) WHEN ID            = '' THEN 'NULL' ELSE CONCAT('''', LTRIM(RTRIM(ID       )), '''') END FROM MappingFile WHERE ColNum = @I)
SET @EDT = (SELECT CASE WHEN SUBSTRING(ExtDate  , 1, 3) = 'Var' THEN LTRIM(RTRIM(RIGHT(ExtDate  , LEN(ExtDate   ) - 3))) WHEN ExtDate       = '' THEN 'NULL' ELSE CONCAT('''', LTRIM(RTRIM(ExtDate  )), '''') END FROM MappingFile WHERE ColNum = @I)
SET @DDT = (SELECT CASE WHEN SUBSTRING(DataDate , 1, 3) = 'Var' THEN LTRIM(RTRIM(RIGHT(DataDate , LEN(DataDate  ) - 3))) WHEN DataDate      = '' THEN 'NULL' ELSE CONCAT('''', LTRIM(RTRIM(DataDate )), '''') END FROM MappingFile WHERE ColNum = @I)
SET @COL = (SELECT Variable  FROM MappingFile WHERE ColNum = @I)
EXEC('
SELECT ''' + @DTS + ''',
' + @ID  + ',
' + @EDT + ',
' + @DDT + ',
''' + @COL + ''',
' + @COL + '        
FROM ' + @DTS + '
WHERE ' + @COL + ' IS NOT NULL
')