I prefer to have the parts of a software system that are good at particular tasks to do those tasks. Case in point: sorting. SQL Server (with proper indexes) is very good at sorting data, so I make sure that most, if not all of the sorting of data in my applications occurs in stored procedures in SQL Server.
This can cause a bit of a problem, however, if you're developing an application that permits the user to sort by different columns. One solution would be to write as many different stored procedures as you have sort criteria. Maintenance would be a nightmare on that so I don't think it's a good idea. Another way would be to parameterize the sort criteria into either a string or magic numbers and use a case statement in the ORDER BY clause to figure out what to sort by. That is the method I often use, and it looks like this:
1: order by
2: case
3: when @sortExpression = 'lastname asc' then CAST(LastName as sql_variant)
4: when @sortExpression = 'firstname asc' then CAST(FirstName as sql_variant)
5: end asc,
6: case
7: when @sortExpression = 'lastname desc' then CAST(LastName as sql_variant)
8: when @sortExpression = 'firstname desc' then CAST(FirstName as sql_variant)
9: end desc
In this example, imagine we are sorting a table with a person's first and last names as fields named (conveniently) FirstName and LastName. The first part permits sorting in ascending order and the second part permits sorting in descending order. The @sortExpression takes the form that .net data controls serve up. It is important to CAST the field names as sql_variant type. In this example, it doesn't really matter, however, if the data types of the columns that are returned from the case statement are different (e.g. an NVARCHAR and a DATETIME), SQL Server will complain because it can't figure out what data type you're trying to return (it will choose the data type of the first element in the CASE statement, and anything that isn't that type or can be implicitly converted to that type, will cause it to have an error at run-time).
Okay, so this solves the immediate problem of being able to sort by parameter, but what if we want to sort by multiple fields? For example, when the user sorts by LastName (as in the example above), shouldn't the software also sort by FirstName as a secondary sort field? Of course it should. Here's how you do that:
1: order by
2: case
3: when @sortExpression = 'lastname asc' then CAST(LastName as sql_variant)
4: when @sortExpression = 'firstname asc' then CAST(FirstName as sql_variant)
5: end asc,
6: case when @sortExpression = 'lastname asc' then CAST(FirstName as sql_variant) end asc,
7: case when @sortExpression = 'firstname asc' then CAST(LastName) as sql_variant) end asc,
8: case
9: when @sortExpression = 'lastname desc' then CAST(LastName as sql_variant)
10: when @sortExpression = 'firstname desc' then CAST(FirstName as sql_variant)
11: end desc,
12: case when @sortExpression = 'lastname desc' then CAST(FirstName as sql_variant) end desc,
13: case when @sortExpression = 'firstname desc' then CAST(LastName as sql_variant) end desc
Notice lines 6, 7, 12, and 13. These lines inject another field into the order by clause after the first case statement has already returned the primary field. They are whole CASE statements all on their own. You can continue to add fields to the sort list in this way until your hands get tired. In this example, I'm sorting by FirstName as a secondary field when the user selects the LastName field to sort by (and vice-versa); you, however, can make it do whatever you want. For example, you might permit the user to sort by HireDTm (representing the date this person was hired), and then by LastName and FirstName using the technique above. If you happen to also need the ability to page the returned data, this method also works perfectly with the ROW_NUMBER() OVER() syntax also introduced in SQL Server 2005. Just put the order by information in the OVER() part.
Of course, nothing is perfect and there are a couple of problems with this method. The first (which I consider minor) is that the user cannot select the secondary (and further) sort fields. I suppose if you really wanted to, you could change the @sortExpression parameter to be something other than what I have above and instead pass in a value that indicates which of a matrix of combinations of sort fields the user has selected, and then write the WHEN parts of the CASE statement for every combination the user could choose. While that would probably work, I'm thinking it's overkill (and a lot of work). You can usually get away with a couple of static secondary field choices, as long as you spend a couple of moments thinking about how the data is used by the user.
The second problem is that this method is not very speedy. In a table with 500,000 rows, it will return in about two seconds (my test table had six columns, one primary key integer IDENTITY column, and five data columns, which were NVARCHAR(50), NVARCHAR(100), INT, INT, and NUMERIC(14, 4)). In a table with over three million rows (3.2 to be exact), it took between 18 and 22 seconds. I didn't do enough tests to see if the speed degradation is linear or geometric, but it's not good, nonetheless. If you use this method, use it on smallish tables.
Currently rated 5.0 by 2 people
- Currently 5/5 Stars.
- 1
- 2
- 3
- 4
- 5