Selecting A Random Amount Of Rows
When I wrote the code for my Gaptcha control, I made one pretty silly mistake. I was developing against a SQL2005 database, but CrankyGoblin itself uses SQL2000.
So when I had a SQL statement like this:
select top @someParam col1, col2
from someTable
It would happily select the first @someParam rows from the table - but on SQL2000 this doesn't work. In SQL2000 you must hard code the value when calling TOP, not pass it some variable.
So how do you get around this? Again, thanks to my mate Noonie, I got an answer. I don't know how it works for large amounts, but in Gaptcha's case it's a pretty small amount (9 normally) you can do this:
set ROWCOUNT @someParam
select col1, col2
from someTable
set ROWCOUNT 0
Pretty neat! Thanks Noonie - I was sure that I needed a temp table for this :)