Dynamic SQL top statement

I needed to have a dynamic query (stored procedure) where I could give the number of rows that has to be returned. I first tried:

declare @rows int
set @rows = 10

SELECT TOP @rows * FROM MyTable

That didn't work!
I got this error when executing the code:
Incorrect syntax near '@rows'.

There are 2 ways to return a certain dynamic recordset.

Solution 1

First of all you could use the ROWCOUNT statement.
This statement stops processing after a certain number of rows have been processed. It works for SELECT, UPDATE and INSERT.

Here's the syntax of how to use the statement:

declare @rows int
set @rows = 10

set rowcount @rows

select 
 * from MyTable

set rowcount 0

Always remember to use SET ROWCOUNT 0 to turn off the row limiter!

Solution 2

Another way to accomplish the dynamic recordset is to make the whole SQL query a variable.

Here's the syntax of how to use the SQL query as a variable:

declare @rows int
set @rows = 10

declare @query nvarchar(MAX)
set @query = 'select top ' + convert(nvarchar, @rows) + 
' * from MyTable'

Execute(@query)

The EXECUTE statement will run any valid SQL statement that you pass it. You can use this to dynamically generate SQL statements at run time. Keep in mind that SQL Server is providing no syntax checks of this statement until it actually runs so be very careful.