sql server语言 - T-SQL脚本在完成查询构建循环后拒绝继续

Question

我的数据库中有一个表，每天自动记录进度。这个脚本…1. 从这个History表中选择所有不同的分包商，并将它们插入到表变量中。2. 选择History表中所有不同的日期。3.构建查询作为varchar，以插入每个分包商(制造商)每天的吨位4. 尝试将构建的变量打印到屏幕上5. 执行非archar SQL(注释掉)

use database666
-- in-memory employee table to hold distinct PHFabricator 
DECLARE @i int;
DECLARE @f int;
DECLARE @CreateTonnageTableQuery NVARCHAR(MAX);
DECLARE @TonnageTableQuery VARCHAR(MAX);
DECLARE @CurrentTonnageQuery VARCHAR(MAX);
DECLARE @SubbsTable TABLE ( sdx int Primary Key IDENTITY(1,1), OrgID int);
DECLARE @DatesTable TABLE ( idx int Primary Key IDENTITY(1,1), History_date datetime);
INSERT @SubbsTable SELECT distinct PHFabricator FROM tblpackagehistory ORDER BY PHFabricator;
INSERT @DatesTable SELECT distinct PHHistory_Date FROM tblpackagehistory ORDER BY PHHistory_Date;
SET @CreateTonnageTableQuery = 'DECLARE @TonnageTable TABLE ([Fabricator_ID] int primary key';
SET @i = 1;
WHILE (@i <= (SELECT COUNT(*) FROM @DatesTable))
BEGIN
    SET @CreateTonnageTableQuery = @CreateTonnageTableQuery + ', [' + (SELECT 'COL'+CONVERT(varchar(6),idx) FROM @DatesTable WHERE idx = @i) + '] float';
    SET @i = @i + 1;
END
SET @CreateTonnageTableQuery = @CreateTonnageTableQuery + '); ' + CHAR(13)+CHAR(10);
DECLARE @currentSubbie int
DECLARE @currentDate datetime
SET @TonnageTableQuery = '';
SET @CurrentTonnageQuery = '';
SET @f = 0
WHILE (@f <= (SELECT COUNT(*) FROM @SubbsTable))
BEGIN
    SET @f = @f + 1;
    SET @currentSubbie = (SELECT OrgID FROM @SubbsTable WHERE sdx = @f);
    SET @CurrentTonnageQuery = 'INSERT INTO @TonnageTable VALUES (' + CONVERT(varchar(6),@currentSubbie);
    SET @i = 1;
    WHILE (@i <= (SELECT COUNT(*) FROM @DatesTable))
    BEGIN
        SET @currentDate = (SELECT History_date FROM @DatesTable WHERE idx = @i); 
        SET @CurrentTonnageQuery = @CurrentTonnageQuery + ', ' + 
        (   SELECT CONVERT(varchar(20),(sum(PHIssued_Tonnage * PHPercent_Overall_Fabricated))) 
            FROM tblpackagehistory 
            WHERE PHFabricator = @currentSubbie AND PHHistory_Date = @currentDate           
        );
        SET @i = @i + 1;
    END
    SET @CurrentTonnageQuery = @CurrentTonnageQuery + '); ' + CHAR(13)+CHAR(10);
    PRINT @CurrentTonnageQuery;
    SET @TonnageTableQuery = @TonnageTableQuery + @CurrentTonnageQuery;
    PRINT CHAR(13)+CHAR(10) + @TonnageTableQuery + CHAR(13)+CHAR(10);
END
print 'just work dammit';
print 'omg ' + @TonnageTableQuery + ' omg';
print 'omfg';
--DECLARE @statement nvarchar(max);
--SET @statement = @CreateTonnageTableQuery + @TonnageTableQuery + 'SELECT * FROM @TonnageTable;';
--EXEC sp_executesql @statement;

总结一下，您将注意到整个代码中的一些print语句，而不仅仅是接近结尾的语句。所有这些工作，查询是按预期构建的，我最终与每个制造商一行，制造商的ID和历史表中每个日期的一个吨位列。然而，在最后一个循环之后，它似乎没有保留任何变量数据:

print 'just work dammit';
print 'omg ' + @TonnageTableQuery + ' omg';
print 'omfg';

输出:

just work dammit
omfg

我哪里错了?

Answer 1

看起来您正在连接NULL和结果为NULL的字符串。这将在整个算法中传播NULLness。您可以使用ISNULL函数将一个适当的字符串(如空字符串或文字字符串NULL)替换为NULL值。

NULL可能来自变量表的末尾。尝试将WHILE语句更改为:

WHILE (@f < (SELECT COUNT(*) FROM @SubbsTable))

Answer 2

进入查询选项并将CONCAT_NULL_YIELDS_NULL设置为false，看看是否会得到输出。如果是这样，其中一个表达式的值可能为null。

NB我不建议将其设置为false，除非用于诊断，这是一个连接级别的设置，可能导致难以调试生产过程中的错误。