Using a parameter in a SELECT TOP statement in SQL

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 https://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.

Comments

• Anonymous
  November 13, 2007
  As usual, this may well be common knowledge but I found this useful. In a stored procedure we were passing

• Anonymous
  November 13, 2007
  Being able to use a variable in the TOP clase was introduced in SQL 2005. Prior to that, you could also limit the number of rows returned by using: SET ROWCOUNT @MaxRows although it wasn't necessarily pretty and does have some limitations.

• Anonymous
  March 20, 2009
  Thanks for this post - you saved me blushes. I was trying to pass the number of rows to be returned using DbParameters, and was getting puzzled by the sqlclient error. Thanks to your tip, I encapsulated the parameter in parenthesis and it worked !

• Anonymous
  December 10, 2010
  It took me a surprisingly long time to find the solution to this problem, and here it lay. Cheers, John.

• Anonymous
  April 01, 2013
  Hi, this might be and old thread but just to be clear, it's parenthesis not brackets and thanks for the tip to making a select top query parameterized.