Top stored-procedures frequently asked interview questions
This is an issue that I've spent hours researching in the past. It seems to me to be something that should have been addressed by modern RDBMS solutions but as yet I have not found anything that really addresses what I see to be an incredibly common need in any Web or Windows application with a database back-end.
I speak of dynamic sorting. In my fantasy world, it should be as simple as something like:
ORDER BY @sortCol1, @sortCol2
This is the canonical example given by newbie SQL and Stored Procedure developers all over forums across the Internet. "Why isn't this possible?" they ask. Invariably, somebody eventually comes along to lecture them about the compiled nature of stored procedures, of execution plans in general, and all sorts of other reasons why it isn't possible to put a parameter directly into an ORDER BY clause.
I know what some of you are already thinking: "Let the client do the sorting, then." Naturally, this offloads the work from your database. In our case though, our database servers aren't even breaking a sweat 99% of the time and they aren't even multi-core yet or any of the other myriad improvements to system architecture that happen every 6 months. For this reason alone, having our databases handle sorting wouldn't be a problem. Additionally, databases are very good at sorting. They are optimized for it and have had years to get it right, the language for doing it is incredibly flexible, intuitive, and simple and above all any beginner SQL writer knows how to do it and even more importantly they know how to edit it, make changes, do maintenance, etc. When your databases are far from being taxed and you just want to simplify (and shorten!) development time this seems like an obvious choice.
Then there's the web issue. I've played around with JavaScript that will do client-side sorting of HTML tables, but they inevitably aren't flexible enough for my needs and, again, since my databases aren't overly taxed and can do sorting really really easily, I have a hard time justifying the time it would take to re-write or roll-my-own JavaScript sorter. The same generally goes for server-side sorting, though it is already probably much preferred over JavaScript. I'm not one that particularly likes the overhead of DataSets, so sue me.
But this brings back the point that it isn't possible — or rather, not easily. I've done, with prior systems, an incredibly hack way of getting dynamic sorting. It wasn't pretty, nor intuitive, simple, or flexible and a beginner SQL writer would be lost within seconds. Already this is looking to be not so much a "solution" but a "complication."
The following examples are not meant to expose any sort of best practices or good coding style or anything, nor are they indicative of my abilities as a T-SQL programmer. They are what they are and I fully admit they are confusing, bad form, and just plain hack.
We pass an integer value as a parameter to a stored procedure (let's call the parameter just "sort") and from that we determine a bunch of other variables. For example... let's say sort is 1 (or the default):
DECLARE @sortCol1 AS varchar(20)
DECLARE @sortCol2 AS varchar(20)
DECLARE @dir1 AS varchar(20)
DECLARE @dir2 AS varchar(20)
DECLARE @col1 AS varchar(20)
DECLARE @col2 AS varchar(20)
SET @col1 = 'storagedatetime';
SET @col2 = 'vehicleid';
IF @sort = 1 -- Default sort.
BEGIN
SET @sortCol1 = @col1;
SET @dir1 = 'asc';
SET @sortCol2 = @col2;
SET @dir2 = 'asc';
END
ELSE IF @sort = 2 -- Reversed order default sort.
BEGIN
SET @sortCol1 = @col1;
SET @dir1 = 'desc';
SET @sortCol2 = @col2;
SET @dir2 = 'desc';
END
You can already see how if I declared more @colX variables to define other columns I could really get creative with the columns to sort on based on the value of "sort"... to use it, it usually ends up looking like the following incredibly messy clause:
ORDER BY
CASE @dir1
WHEN 'desc' THEN
CASE @sortCol1
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END DESC,
CASE @dir1
WHEN 'asc' THEN
CASE @sortCol1
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END,
CASE @dir2
WHEN 'desc' THEN
CASE @sortCol2
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END DESC,
CASE @dir2
WHEN 'asc' THEN
CASE @sortCol2
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END
Obviously this is a very stripped down example. The real stuff, since we usually have four or five columns to support sorting on, each with possible secondary or even a third column to sort on in addition to that (for example date descending then sorted secondarily by name ascending) and each supporting bi-directional sorting which effectively doubles the number of cases. Yeah... it gets hairy really quick.
The idea is that one could "easily" change the sort cases such that vehicleid gets sorted before the storagedatetime... but the pseudo-flexibility, at least in this simple example, really ends there. Essentially, each case that fails a test (because our sort method doesn't apply to it this time around) renders a NULL value. And thus you end up with a clause that functions like the following:
ORDER BY NULL DESC, NULL, [storagedatetime] DESC, blah blah
You get the idea. It works because SQL Server effectively ignores null values in order by clauses. This is incredibly hard to maintain, as anyone with any basic working knowledge of SQL can probably see. If I've lost any of you, don't feel bad. It took us a long time to get it working and we still get confused trying to edit it or create new ones like it. Thankfully it doesn't need changing often, otherwise it would quickly become "not worth the trouble."
Yet it did work.
My question is then: is there a better way?
I'm okay with solutions other than Stored Procedure ones, as I realize it may just not be the way to go. Preferably, I'd like to know if anyone can do it better within the Stored Procedure, but if not, how do you all handle letting the user dynamically sort tables of data (bi-directionally, too) with ASP.NET?
And thank you for reading (or at least skimming) such a long question!
PS: Be glad I didn't show my example of a stored procedure that supports dynamic sorting, dynamic filtering/text-searching of columns, pagination via ROWNUMBER() OVER, AND try...catch with transaction rollbacking on errors... "behemoth-sized" doesn't even begin to describe them.
Update:
- I would like to avoid dynamic SQL. Parsing a string together and running an EXEC on it defeats a lot of the purpose of having a stored procedure in the first place. Sometimes I wonder though if the cons of doing such a thing wouldn't be worth it, at least in these special dynamic sorting cases. Still, I always feel dirty whenever I do dynamic SQL strings like that — like I'm still living in the Classic ASP world.
- A lot of the reason we want stored procedures in the first place is for security. I don't get to make the call on security concerns, only suggest solutions. With SQL Server 2005 we can set permissions (on a per-user basis if need be) at the schema level on individual stored procedures and then deny any queries against the tables directly. Critiquing the pros and cons of this approach is perhaps for another question, but again it's not my decision. I'm just the lead code monkey. :)
Source: (StackOverflow)