Code to Write Code

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
			1.1.1.1 detail one
			1.1.1.2 detail two

We have a stored procedure that will let you pass in 1.1.1.2 and get all the descriptions down to that level, so the “full description” of 1.1.1.2 would be:

1. chapter
	1.1 heading
		1.1.1 sub-heading
			1.1.1.2 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!