Have you ever needed to build a stored procedure that would take the name of a column as a parameter, and return the results ordered by that column?
So have I, so after researching it for a couple of days, and none of the forums had any good solutions; All that he was able to find was a reference from Microsoft that said that the only way to do it was to build your SQL statement dynamically as a string in the stored procedure and EXEC it (not very good, considering it wouldn’t be able to leverage the T-SQL code optimiser – you’d be as well building it in your given language). I spent some time working on this and came up with a much better solution. There is a way to accomplish dynamic column sorting, while still leveraging a fully compiled and optimised stored procedure.
Microsoft says that you can’t use a variable for the column in an ORDER BY statement, and most attempts gave us a “can’t convert this value to an int” error. There is a way to make it work, though.
Consider a table:
ID int,
FirstName nvarchar(20),
LastName nvarchar(20),
ApplyDate datetime
We want to call a stored procedure, passing the name of the column that we want the records to be sorted by:
GetApplications(@SortField nvarchar(20))
Here’s what the T-SQL stored procedure definition looks like:
create proc GetApplications
@SortField nvarchar(20)
as
select * from Applications
order by
case @SortField
when 'FirstName' then cast (FirstName as sql_variant)
when 'LastName' then cast (LastName as sql_variant)
when 'ApplyDate' then cast (ApplyDate as sql_variant)
else cast (ID as sql_variant)
end
That’s it! Be sure to put the ELSE clause in there as a catch-all, so that you handle columns that don’t exist or typos in the value passed into the stored procedure.
You also need to cast all the values to sql_variant so that the ORDER BY clause won’t get confused about the type of the column that we’re sorting by, and try to force our values into an int.
This is tried and tested SQL Server 2000, and it works just fine. No promises for expermmenting with older/newer versions!
I’m always looking to improve my own SQL (SQL Server, MySQL are my main databases but always keen to learn new things too!) knowledge… so contact me if you have other hints or tips!