Looping through query values using WHILE in SQL Server

I recently had an interesting requirement from a client that entailed posting values in an XML field in SQL Server out to a message queue. This necessitated querying the XML field in the database based on certain parameters, and then looping through and posting each XML value into MSMQ. I won’t go into detail on MSMQ in this post, but I thought I would show how I looped through query values, since it is a useful pattern.

I started with a file name as an input parameter:

@FileName nvarchar(200) = NULL

I then added some variables: xml (set as varchar(max) since that’s what the external MSMQ assembly accepts), a count total that represents the number of rows in my query, a counter, and a table which holds the results of my query. For some reason I had to do two DECLARE statements to get the table variable to work in SQL Server 2005.

@xml varchar(max)
,@orderCount int
,@counter int

-- Sets up temp table to hold XML for loop
@tempTable table(id int identity(1,1),xml_msmq xml)

Finally I set up the loop. First I populate tempTable with XML using a query with various parameters. Since I already set up an autoincrement identity key (ID) in tempTable, we will have something to use for our loop. Then I set the counter variable to the MIN value for ID in tempTable, and orderCount to the MAX value. Finally, I use the WHILE expression to loop through all of the records in tempTable, posting the xml to another sproc (AddXMLMaxToMSMQ) which sends it out to MSMQ.

INSERT INTO @tempTable(xml_msmq) SELECT XML_Body FROM OtherTable WHERE ID = (SELECT ID FROM someOtherTable WHERE [FileName] = @FileName) AND [STATUS] = 2
SELECT @counter = MIN(id) FROM @tempTable
SELECT @orderCount = MAX(id) FROM @tempTable
--Setup loop
WHILE (@counter <= @orderCount)

SET @xml = (SELECT xml_msmq FROM @tempTable WHERE ID = @counter)

--Call the sproc wich sends the xml to MSMQ
EXEC AddXMLMaxToMSMQ @xml, @serverName, @queueName, @queueType
--Increment the counter
SET @counter = @counter + 1



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s