Using a parameter in a SELECT TOP statement in SQL13 Nov 2007
Originally posted on my old MSDN blog
As usual, this may well be common knowledge but I found this useful.
In a stored procedure we were passing in a parameter (say @maxRows) which we wanted to use to dynamically limit the number of rows returned by the query.
For ages we couldn't find a way to do this without building up the SQL in a string
using the parameter and then executing it e.g. something horrible like
'SELECT TOP ' + CAST(@MaxRows AS varchar(10)) + '...'
Now it turns out you are supposed to put the value used by the TOP in brackets anyway - see http://msdn2.microsoft.com/en-us/library/ms189463.aspx - so putting the parameter in brackets e.g. SELECT TOP (@MaxRows) ... is not only correct but gets around the horrible cast.