CODEWITHSUNDEEP
sqlsql-servert-sqlvariablesloops
user544141
user544141

Reputation: 90

T-SQL script refuses to continue after finishing a query-building loop

I have a table in my database that automatically records progress, day by day. This script... 1. Selects all distinct sub-contractors from this History table and inserts them into a table variable. 2. Selects all distinct dates in the History table. 3. Builds a query as a varchar to insert day-by-day tonnage per Sub-contractor (fabricator) 4. Attempts to print-to-screen the built variable 5. Executes the nvarchar'd SQL (commented out)

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;

To summarise, you will notice some print statements throughout the code, not just the ones near the end. All these work, the query is building as intended, I end up with one line per fabricator, with the fabricator's ID and one tonnage column per date in the History table. However after the final loop, it doesn't seem to retain any variable data:

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

outputs:

just work dammit

omfg

Where am I going wrong?

Upvotes: 1

Views: 177

Answers (2)

cmsjr
cmsjr

Reputation: 59205

Go to Query options and set CONCAT_NULL_YIELDS_NULL to false, and see if that gets you output. If so, one of your expressions is probably evaluating to null.

NB I don't recommend leaving it set to false other than for diagnostics, it's a connection level setting and could result in difficult to debug errors for production procs.

Upvotes: 0

Jake T.
Jake T.

Reputation: 631

It looks like you are concatenating NULL and a string which results in NULL. This will propagate the NULLness throughout your whole algorithm. You can use the ISNULL function to substitute an appropriate string (like an empty string or the literal string NULL) for a NULL value.

The NULL might be coming from falling off the end of your variable table. Try changing your WHILE statement to:

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

Upvotes: 1

Related Questions