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.