Post reply

SELECT TOP X by using dynamic SQL

  • 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?

  • February 14, 2005 at 12:57 pm

    #542143

    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

  • February 14, 2005 at 1:18 pm

    #542147

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



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • February 15, 2005 at 1:59 am

    #542213

    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]

  • February 15, 2005 at 8:48 am

    #542309

    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'

  • February 15, 2005 at 10:04 am

    #542331

    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.

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

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