[SQL] Using a Variable TOP Clause
This is a simple one, but still worth mentioning. Say you want to select a variable number of rows in a SELECT statement and don't want to use "dynamic SQL" to do that but rather use a SQL Parameter - no problems. In SQL Server 2005 you can type a query like this:
SELECT TOP (@number) Title,Body,Author,Created,LastModified FROM Pages ORDER BY LastModified DESC
Note that the parenthesis around @number is important, or you'll end up getting this error:
Incorrect syntax near '@number'.
If you're stuck with an older version of SQL Server, there are several ways to work around that problem. You could for example set the ROWCOUNT, as ROWCOUNT supports a variable:
SET ROWCOUNT @number; SELECT Title,Body,Author,Created,LastModified FROM Pages ORDER BY LastModified DESC; SET ROWCOUNT 0