Utilizing table variable and while loop to avoid cursor-style operations

May 7, 2010 by Ben Galluzzo    SQL |    Comments (0)

So, here is one of many scenarios in which the work performed can be accomplished by use of a cursor.  For a long running set of queries such as what's listed below, utilizing a cursor could have undesired painful effects such as blocking.  In a tough spot, those who look toward a procedural approach in SQL often quickly jump to using cursors.

Here the scenario is described.  There are two dissimilar systems. The first is the old system which will still be in production for an indefinite period of time.  The old system is actually comprised of multiple systems on multiple SQL servers; one for each client.  The second, the replacement system, is running along side the old system and is managing different data of the same type of business process.  Specific work data is to be retrieved from the old system and married to the work data being retrieved from the new system so team leads can keep tabs on the work data without going to both systems.

It's actually fairly easy to utilize a table variable and a while loop to avoid these cursor-style operations.  Here is an example of a very simplified operation to obtain the related client data from the older system in preparation for marrying it to the related client data from the new system.  This example also briefly looks at a way to generate dynamic SQL. There probably is a better way to do this type of work, but it is indeed easy enough for most to be able to wrap their mind around.

The key pieces of the query are the table variable containing the list of client databases, an iterator, a total row count variable, and the while loop.  

The table variable, of course, will only persist in the life time of the SQL connection.  Once the query has completed, the table variable is gone.  Creating the table variable with an auto-incrementing integer identity field provides for the ability to actually loop through the work list.

DECLARE @clientData table (                      
    RowNum int IDENTITY (1, 1) Primary key NOT NULL,                      
    ClientID uniqueidentifier,          
    ClientName varchar(100),               
    ClientSource varchar(50)  --location of old system
)                 

 

Now, populate the temp table with all of items for with which to work.  Next, is the preparation of the variables to be able to run through the loop properly.

DECLARE @rowCount int                      
DECLARE @maxRows int                    
                      
SELECT @rowCount = 1            
          
SELECT @maxRows = count(*) from @clientData          
PRINT @maxrows

 

Then the loop is built; being sure that the @rowCount variable is incremented by one for the next return back through the loop.  Note how the current temp table row data can be accessed for use inside the loop. 

WHILE @rowCount <= @maxRows                      
BEGIN              
    DECLARE @clientID uniqueidentifier          
    SET @clientID = (SELECT ClientID FROM @clientData WHERE rowNum = @rowCount)          
        
    DECLARE @clientName varchar(100)          
    SET @clientName = (SELECT ClientName FROM @clientData WHERE rowNum = @rowCount)          
              
    DECLARE @sourceName varchar(50)                      
    SET @sourceName = (SELECT ClientSource FROM @clientData WHERE rowNum = @rowCount)                      
          
    DECLARE @sql varchar(4500)          
          
    SET @sql = '          
    INSERT INTO WorkList          
    SELECT
        '''+@clientID+''' ClientID,
        WI.create_date CreatedDate,       
        '''+@clientName+''' Client,          
        ....
        ....
        ....
        ....
        ....
        ....
        ....
    LEFT JOIN          
        ' + @sourceName + '.dbo.WorkItem WI
    ON          
    '                            
    EXEC SP_EXECUTESQL @SQL
          
    SELECT @rowCount = @rowCount + 1   
END

 

To provide the big picture, here is the query in its entirety. 

-------------------------------------------------------          
-- Begin - Obtain client list          

DECLARE @clientData table (                      
    RowNum int IDENTITY (1, 1) Primary key NOT NULL,                      
    ClientID uniqueidentifier,          
    ClientName varchar(100),               
    ClientSource varchar(50)  --location of old system
)                 
          
INSERT INTO @clientData (ClientID, ClientName, ClientSource)          
SELECT          
    C.ID ClientID,          
    C.[Name] ClientName,            
    '[' + M.cServer + '].' + M.cDatabase ClientSource              
FROM [ModernServer].CoreClient.dbo.Client C
LEFT JOIN Mapping.dbo.Client M
ON C.MappingClientID = M.cID                  
WHERE C.IsActive = 1                      
          
-- End - Obtain client list          
-------------------------------------------------------          
          
-------------------------------------------------------                                  
-- Begin - Create temp table                                  
                                  
IF object_id('WorkList') IS NOT NULL                                  
BEGIN                                  
    DROP TABLE WorkList
    PRINT 'Table Dropped'                                  
END                                  
                                  
CREATE TABLE WorkList (
    ClientID uniqueidentifier,
    CreatedDate datetime,    
    ClientName varchar(100),
    CreatedByName varchar(100),          
    ProblemCode varchar(50),          
    CustomerName varchar(100),          
    AccountNumber varchar(50),          
    StatusType varchar(25),
    LastUpdateDate datetime,
    UpdatedByName varchar(100)
)                                  
     
-- End - Create temp table                               
-------------------------------------------------------           

DECLARE @rowCount int                      
DECLARE @maxRows int                    
                      
SELECT @rowCount = 1            
          
SELECT @maxRows = count(*) from @clientData          
PRINT @maxrows
          
WHILE @rowCount <= @maxRows                      
BEGIN              
    DECLARE @clientID uniqueidentifier          
    SET @clientID = (SELECT ClientID FROM @clientData WHERE rowNum = @rowCount)          
        
    DECLARE @clientName varchar(100)          
    SET @clientName = (SELECT ClientName FROM @clientData WHERE rowNum = @rowCount)          
              
    DECLARE @sourceName varchar(50)                      
    SET @sourceName = (SELECT ClientSource FROM @clientData WHERE rowNum = @rowCount)                      
          
    DECLARE @sql varchar(4500)          
          
    SET @sql = '          
    INSERT INTO WorkList          
    SELECT
        '''+@clientID+''' ClientID,
        WI.create_date CreatedDate,       
        '''+@clientName+''' Client,          
        ....
        ....
        ....
        ....
        ....
        ....
        ....
    LEFT JOIN          
        ' + @sourceName + '.dbo.WorkItem WI
    ON          
    '                  
          
    PRINT (@SQL)
    EXEC SP_EXECUTESQL @SQL
          
    SELECT @rowCount = @rowCount + 1   
END

 

That's all there is to it.  This is just one example of so many others to use a temp table or table variable and while loop in lieu of a cursor.   


SQL Saturday - 506 - Baltimore BI Edition

Month List