I’ve read “When you start writing code that writes code for you, you’ve moved up from a beginner to a professional.” Today I’d like to walk you through an example of where learning to write this kind of code pays off.
At my we have a table that defines a hierarchy. Each level on the hierarchy has a description. Here’s a very generic example:
1. chapter 1.1 heading 1.1.1 sub-heading 220.127.116.11 detail one 18.104.22.168 detail two
We have a stored procedure that will let you pass in 22.214.171.124 and get all the descriptions down to that level, so the “full description” of 126.96.36.199 would be:
1. chapter 1.1 heading 1.1.1 sub-heading 188.8.131.52 detail two
After a while we determined that building this description on the fly was too expensive in terms of time. We decided to build a column to store this long description, eventually we would make this column a computed column and the long description would update whenever a change was made to the record.
Before we could make that change, we had to compute all the long descriptions and store them in the column. Rather than executing hundreds of thousands of stored procedures I built a query that created the execute statement for me, and let that take care of this process.
For the purpose of this demo, we’re not going to get into how the stored procedure builds the hierarchy, then stores it. We’re just going to assume it does that part. We pass in the ID for the record, and it fills in the long description for us.
First get a list of the records you need to process.
SELECT ID FROM workingTable WHERE LongDescription IS NULL
That list of IDs will form the basis of our WHILE loop. For each one of those, we’re going to run the stored procedure.
WHILE EXISTS ( SELECT ID FROM workingTable WHERE LongDescription IS NULL) BEGIN END
With this WHILE loop, we can do something for each ID found. Be careful you remove records from that list in the body of the loop, or you’ll never exit the loop. We’ll take care of that, by adding our EXECUTE step. To do the EXECUTE step, we need to set up a variable to hold the ID for processing.
DECLARE @ID INT WHILE EXISTS ( SELECT ID FROM workingTable WHERE LongDescription IS NULL) BEGIN SELECT TOP 1 @ID = ID FROM workingTable WHERE LongDescription IS NULL END
At this point, we can’t run this code since we’d never leave the loop, but with one more step, we can actually process each ID, which updates Long Description and removes it from the results returned in the EXISTS check.
DECLARE @ID INT WHILE EXISTS ( SELECT ID FROM workingTable WHERE LongDescription IS NULL) BEGIN SELECT TOP 1 @ID = ID FROM workingTable WHERE LongDescription IS NULL EXEC usp_storeLongDescription @ID END
Now each time it hits this loop, it will create the long description, then update the LongDescription field, changing it from NULL to a defined value.
This makes the process far easier, than writing several thousand EXEC usp_storeLongDescription statements!
If you have any questions about this process, please let me know. I’m here to help!