Post reply

Dynamic "TOP" values in select statements

  • October 25, 2007 at 12:41 pm

    #178796

    I know there has to be a way to dynamically tell a select statement the top value without having to build the sql and then use the exec command. I tried this

    declare @top int

    set @top = 100

    select top @top policynumbers from tblpolicynumbers

    and that didn't work, obviously. Does anyone know how I can accomplish this?

    Thanks!

    Michael

  • October 25, 2007 at 1:31 pm

    #745474

    Only way I have ever heard of is with dynamic sql.

  • October 25, 2007 at 1:55 pm

    #745480

    You want to use SET ROWCOUNT @top before your query and SET ROWCOUNT 0 afterwards.

    DECLARE @top int

    SET @top = 100

    SET ROWCOUNT @top

    SELECT policynumbers FROM tblpolicynumbers

    SET ROWCOUNT 0

  • October 25, 2007 at 2:15 pm

    #745487

    You should review BOL for SET ROWCOUNT as there are warnings about using this in updates,deletes, and inserts.

    If you are just doing selects then you should be ok.

  • October 26, 2007 at 12:36 am

    #745589

    This works in SQL 2005, but ot in SQL 2000. In 2000, the only ways to do this are dynamic SQL or RowCount

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 26, 2007 at 2:52 am

    #745620

    two ways

    1

    DECLARE @top int

    SET @top = 100

    SET ROWCOUNT @top

    SELECT policynumbers FROM tblpolicynumbers

    SET ROWCOUNT 0

    2

    DECLARE @top INT

    SET @top = 100

    EXEC('SELECT TOP ' + @top + ' policynumbers FROM tbl')

  • October 26, 2007 at 7:11 am

    #745692

    Here is basically what I plan to do

    declare @MaxRecords int

    select @MaxRecords = MaxRecordsToArchive from ArchiveControl where TableName = 'PolicyProcessAudit'

    set rowcount @MaxRecords

    --Archive the top x records

    insert into dbo.PolicyProcessAudit

    select * from db..PolicyProcessAudit (nolock)

    --delete the top x records from the production table

    delete from db..PolicyProcessAudit

    set rowcount 0

    I think that would work, but wanted to run it by some others before I tested it out....

    Thanks,

    Michael

  • October 26, 2007 at 8:06 am

    #745729

    Put an order by on your select and your delete, or there's no guarentee that you'll get the same records. Especially since you have a nolock on the select.

    Without an order by, SQL makes no guarentees about what order rows are returned in, and hence what rows get caught in a top x

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

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