Using a parameter in a SELECT TOP statement in SQL

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.