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!

Sorting by Column, SQL, SQL Server, Stored Procedure Hide

OK, so I’ve been using my Mac for a while now… and I must admit it has grown on me hugely over the time I have used it – I was sceptical about using a Mac, but I do slightly prefere it now. Due to software I use for development I’ll always be a dual user just some things I need the PC for – anwayway… I’m here to talk about iMovie ‘08.

I’ve done some video editing in the past on the PC, and Mac using professional stuff… however I was viewing my growing pile of MiniDV tapes for my camcorder and decided I should get them to video/dvd. Not wanting to spend hours/days editing I decided to try iMovie. I’ve never used it so I went in cold and was surprised and very happy with my end product – between iMovie and iDVD that is.

First step was to import my video, which was very easy and ‘quick’ though i did have to watch it play in realtime as it imported – perhaps there is a way to do it quicker?

After that was done, it was pretty simple to move my clips to edit them and compose them … it is very simple and as a result there is obviously a loss of control if you are used to Final Cut or similar on the PC… but if it’s just to get that holiday footage tided up and onto dvd then it’s probably more than sufficient.

After some resizing of clips, I became adventurous and – gasp – added titles and transitions (though not too much of these as I decided they were a bit corny) … next I added some music to liven it up.

So to create my wee video of a recent trip to Dubai, took (excluding importing time) just a little over an hour to compose, and a matter of moments in iDVD.

Burning the DVD in iDVD was quick and painless too… pick a theme, drop the movie in, give it a title and click a button and that was it really.

I was paranoid about quality… video on a computer in my past experiance was never that great. So I tentativley shoved it in the DVD player and was very happy with the resulting video. If anyone knows a good FREE resoource to upload video too I may upload some later.

One of the odd things in iMove is there is no need to save, but it does indeed save as you go – I quit a few times to test it!

No tags Hide