SELECT TOP X by using dynamic SQL

Question

Post reply

SELECT TOP X by using dynamic SQL

ganiered

SSC Enthusiast

Points: 192
More actions
February 14, 2005 at 12:26 pm

#65650

I am writing a report where the user needs to be able to specify how many records they want returned. Is there a good way to do the following:
DECLARE @Top int
SELECT TOP @Top FROM ....
I know that this won't work as it is but is there a way I can get the same effect?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 1

You could do something like this:

DECLARE @Count INT

DECLARE @Query VarChar(700)

SELECT @Count = 5

SELECT @Query = ('SELECT TOP ' + STR(@Count) + ' * FROM table name')

EXEC (@Query)

Good luck,

Darrell

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 2

This is one feature that SQL2K5 has finally been able to provide SANS dynamic SQL..

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Just for the records: You can also use SET ROWCOUNT to limit the resultset. You can also use this with a variable.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

cliffb SSCarpal Tunnel Points: 4598 More actions · Answer 4

If you are concerned about NULLS for your variable, you can also do this:

SET @sql = 'SELECT TOP ' + IsNull(str(@number),'100 PERCENT') + ' * FROM table_name'

Antares686 SSC Guru Points: 125444 More actions · Answer 5

But keep in mind Rowcount may not return the same records as top does. So it depends on a few factors but make sure you test that you get the right results based on your where and order by conditions.