SQL Server Performance

Change SELECT TOP [N] feature programmatically in stored procedure

Discussion in 'T-SQL Performance Tuning for Developers' started by abradley81, Oct 3, 2007.

  1. abradley81 New Member

    I've got a select statement I want to tell how many rows I want to select.
    Is there a way to do something along these lines
    DECLARE @numOfRows int
    SELECT TOP @numOfRows column FROM table
    How do you do this in t-sql?
    abradley81, Oct 3, 2007
    #2

  2. ndinakar Member

    Doesnt what you have work?
    Another way to do it is to use SET ROWCOUNT @numofRows.
    ndinakar, Oct 3, 2007
    #3

  3. Adriaan New Member

    Correct, in SQL 7.0 and 2000 you can only use
    SET ROWCOUNT @var
    In SQL 2005, you can use a variable in the TOP clause ...
    SELECT TOP @var
    Adriaan, Oct 4, 2007
    #4

  4. Madhivanan Moderator

    Or with the hlep of dynamic sql

    DECLARE @numOfRows int, @sql varchar(8000)

    set @numOfRows=1
    set @sql='SELECT TOP ' + cast(@numOfRows as varchar(5))+' column FROM table'
    exec(@sql)
    Madhivanan, Oct 4, 2007
    #5

  5. abradley81 New Member

    Sorry, forgot to mention I have SQL 2000.
    But SET ROWCOUNT @var works beautifully!!!
    THANKS!
    abradley81, Oct 4, 2007
    #6

  6. Adriaan New Member

    Don't forget to add SET ROWCOUNT 0 after the SELECT - just in case you ever want to add new queries to the sp.
    Adriaan, Oct 4, 2007
    #7

Share This Page