Ready, Steady, Code

Once or twice while I was on leave recently, I happened to have the TV on during the daytime. One of the programs that was on was Ready, Steady, Cook (originally a BBC show apparently).

The basic format of the show is as follows:

• In the first half, two chefs (each with a regular assistant) create a meal from some random ingredients that they haven’t seen before.
• The audience then votes on the completed dishes.
• The second half sees both chefs then suggest a meal from another lot of ingredients, but this time the audience votes on which idea they like the best. Then both chefs work together to make that meal.

So what I was thinking was whether you could adapt this format to a user group setting?

Have two teams (ideally with two data projectors so the audience can see what they’re doing), and give each of them 3-4 random technologies and they have to come up with an application that uses all of them in a fixed time period.

At the conclusion the audience can vote on which they think is the best.

You might need a little more time, and the audience could get involved shouting out suggestions if they get stuck.

Ordering data using dynamic SQL versus temporary tables

Here’s a scenario that’s not that uncommon:

You have a query and the user wants to be able to choose at run-time which column to sort/group by.

The problem is you can’t use a variable to indicate the column name in the ORDER BY or GROUP BY clauses.

If the query itself is very simple, then you could just repeat the query with specified ordering using IF. eg.

IF (@order = ‘Column1’)

SELECT * from Table1 ORDER BY Column1

ELSE IF (@order = ‘Column2’)

SELECT * from Table1 ORDER BY Column2

If the query is non-trivial, you probably wouldn’t want to use this however, as maintenance becomes problematic.

There are two obvious solutions to this:

Dynamic SQL

Assemble the query into a varchar variable and then execute it using sp_executesql.

DECLARE @sql varchar(4000)

SET @sql = ‘SELECT * FROM Table1’

IF (@order = ‘Column1’)

SET @sql = @sql + ' ORDER BY Column1'

ELSE IF (@order = ‘Column2’)

SET @sql = @sql + ' ORDER BY Column2'

sp_executesql @sql

Temporary Tables

Split the query into two parts. First of all, retrieve the data into a temporary table (this is one of the few times you can’t use a table variable). Next, use the original IF .. ELSE IF method to return the data from the temporary table.

SELECT * INTO #Stuff FROM Table1

IF (@order = ‘Column1’)

SELECT \* FROM #Stuff ORDER BY Column1

ELSE IF (@order = ‘Column2’)

SELECT \* FROM #Stuff ORDER BY Column2

So which is better? Great question, and I’m not really sure of the answer. I suspect it could well be “it depends”.

Copy Full Path for Windows XP

I’m missing the “Copy Full Path” feature from Windows Explorer in Vista now that I’m back in WinXP land. And that’s where Copy Path to Clipboard comes into play.