In my on-going efforts to expand my horizons in the land of SQL Server, I’m going to discuss recursive queries using the SQL Server feature called Common Table Expressions or CTEs. First off, a CTE is essentially a temporary result set that is available only in the scope of the query that creates it.
Here’s a quick example:
WITH TempCrap(CrapID, CrapTitle, CrapDescription, CrapQty) AS ( SELECT CrapID, CrapTitle, CrapDescription, CrapQty FROM Crap WHERE CrapQty > 10 ) SELECT CrapID, CrapTitle, CrapDescription, CrapQty FROM TempCrap ORDER BY CrapTitle
Alright, I’m fully aware that this is a lame example, but I will get to something a little more interesting in a moment. I’m not going to go into full detail on the whole CTE thing, but you can find more info on the web. The real goal here is to flex the power of the CTE to do recursive queries. Why would I need to do a recursive query, you ask? Well, you might have to be a little crazy, but there are some situations that recursion fits the bill. Two words, nested categories.
Let’s say that you have a table with a CategoryId and an optional field, ParentId. ParentId, when it is not null, it’s value will refer to the CategoryId of its parent. How about some example data to work with?
There’s just enough data to get the point across. You’ll notice that Car and SUV have no ParentId, which makes them root nodes. You’ll also notice that Corvette, Porsche and Tahoe all have values for their ParentId, which makes them the child nodes. Given this data, let’s assume that you want to get all Cars. With our data, this would be pretty simple, but humor me and pretend there is more nesting going on with more data. Let’s take a look at what we could do to accomplish this.
WITH TempCategories(CategoryId, CategoryName, ParentId, Level) AS ( SELECT CategoryId, CategoryName, ParentId, 0 AS Level FROM Category WHERE CategoryName LIKE 'Car' UNION ALL SELECT C.CategoryId, C.CategoryName, C.ParentId, Level + 1 FROM Category AS C INNER JOIN TempCategories AS T ON C.CategoryId = T.ParentId ) SELECT CategoryId, CategoryName, ParentId, Level FROM TempCategories
Since we’ve already discussed what the whole WITH thing is (CTE), so lets get into what is going on under the hood. There are two selects going on which get unioned together (merged). The first select is pretty straight-forward, just grabbing the record who’s CategoryName is ‘Car’. Then, the next step is to get all the descendants of the ‘Car’ category. That’s where things get recursive and interesting.
Check this out:
SELECT C.CategoryId, C.CategoryName, C.ParentId, Level + 1 FROM Category AS C
This is pretty straight-forward also, just grabbing the info we want from the Category table, incrementing the Level. The INNER JOIN part is where the recursion comes in:
SELECT C.CategoryId, C.CategoryName, C.ParentId, Level + 1 FROM Category AS C INNER JOIN TempCategories AS T ON T.CategoryId = C.ParentId
Notice that Category is being joined to the very CTE that the query is building. To get the proper hierarchy, the CategoryId of TempCategories is bound to the ParentId of Category. The data that will be returned here is the entire tree beneath the ‘Car’ node.
There we have it! I hope this helps at least a little!
Working with SQL Server over the years has made me realize just how much learning I have to do before I can classify myself a database guru. Along the way, I have learned a lot of cool things from various sources, but still have plenty more areas to study. Most of the things that I’ve learned about SQL Server, as well as many other things, have come from a need for a solution to a particular situation. What I will be discussing today came from one of those situations.
The problem I had to solve involved querying the database with several different parameter options. The same data was to be retrieved with each query, but it was the filtering of the data that changed. My initial reaction was to write a stored procedure for each of the different parameter permutations. The problems with that approach are numerous, so let’s just say it was too much of a waste of time. I started looking for alternatives and found this blog post discussing the basics of just what I needed to do. Since I’m using this blog as a way for me to archive some of the cool things I’ve learned, I’ll put my own spin on the topic here.
The basics are this: make a single stored procedure that will take in all the possible parameters, giving each one a default value of NULL in case it is not needed, and return the necessary data filtered accordingly. Sound simple? Actually, it really is. Here is an example of a stored proc that does just that:
CREATE PROCEDURE GetCrap @CategoryID int = NULL, @MinPrice decimal(4,2) = NULL, @MaxPrice decimal(4,2) = NULL AS BEGIN SELECT * FROM TableWithCrap T WHERE (@CategoryID IS NULL OR T.CategoryID = @CategoryID) AND (@MinPrice IS NULL OR T.Price >= @MinPrice) AND (@MaxPrice IS NULL OR T.Price <= @MaxPrice) END
See how simple it is? Simply give the optional parameters a default NULL value (@CategoryID int = NULL). Then, and here’s the cool part, make each part of the WHERE clause a conditional that will either evaluate to true if the parameter is null or evaluate according to the requirement.
@CategoryID IS NULL
will return true if the parameter CategoryID has not been given, thus eliminating it from the filtering process.
@CategoryID IS NULL OR T.CategoryID = @CategoryID
basically says, "if I have a value, use me."
That’s it! AND those bad boys together and you’ve got a respectable dynamic query built right into a nice stored procedure! Isn’t that cool?