First published on MSDN on Jun 08, 2009
I was recently building a SQL query for use in a report and was looking for a way to return the ''top N’ results from a table matching my query criteria. No big deal, right? We do this all the time with a simple statement similar to
Select TOP 10 <what you want to display> from <tablename>
Ah, but what if you want to use a variable to specify the value for TOP? Give it a try and you will quickly find that this simple syntax doesn’t work. So how do you make this work then? A little trick I found that works great is to just wrap the query you want with the rowcount variable – similar to
declare @v int
set @v = 6
set rowcount @v
select * from alert
set rowcount 0
With this simple example you can see that rowcount CAN be set with a variable. Using the rowcount option is functionally the same as TOP as it only returns the number of rows specified by rowcount. Notice the last entry where rowcount is set to 0. Zero in this case clears out row count effectively taking away the filter. You will want to be SURE to clear the rowcount filter after your query or all subsequent queries will have the rowcount value applied
