Post reply

SELECT TOP X by using dynamic SQL

  • February 14, 2005 at 12:32 pm

    #102389

     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 2:27 pm

    #542154

    I've heard the next release of SQL Server will support dynamic TOP(n) like you've written, but until then, some dynamic SQL should do the job:

    DECLARE @top varchar(10)

    DECLARE @sql varchar(100)

    SET @TOP = 2

    SET @sql = 'SELECT TOP ' + @top + ' Field FROM MyTable'

    PRINT @sql 

    EXEC(@sql)

     

  • February 15, 2005 at 2:12 am

    #542220

    Consider the following:

    declare @rowcount int

    set @rowcount = 100

    set rowcount @rowcount

    select * from MyTable

     



    Bye
    Gabor

  • February 15, 2005 at 2:14 am

    #542221

    Please refrain from posting the same question multiple times here!

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=161542

    --
    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 7:29 am

    #542272

    I use this, it's not for reports but for data sampling:

     

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    ALTER  PROCEDURE NTH

    @table varchar(100),

    @destination varchar(100),

    @number varchar(100)

    AS

    DECLARE @sql varchar(2000)

    select @sql='select top '+@number+ ' *

    into ' +@destination+

    ' from ' +@table+

    ' order by NEWID()'

    exec(@sql)

     

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

     

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

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