SQL Server Performance

Select Top (Variable) doesn't work

Discussion in 'SQL Server 2005 General Developer Questions' started by Sheetal, Oct 12, 2007.

  1. Sheetal New Member

    Hi,
    The below query doesn't work for me. [:'(]
    DECLARE @int INT
    SET
    @int = 4WHILE
    (@int)<10BEGIN
    SELECT
    TOP @int * FROM Pubs..salesSET
    @int = @int+1END
    Is there any way to select different no of records in every iteration? Please reply.
    Regards,
    Sheetal
  2. Adriaan New Member

    No idea why i doesn't work, but you could try dynamic SQL -
    DECLARE @int INT, @SQL VARCHAR(100)
    SET @int = 4
    WHILE (@int)<10
    BEGIN
    SET @SQL = 'SELECT TOP ' + CAST(@int AS VARCHAR(2)) + ' * FROM Pubs..sales'
    EXEC (@SQL)
    SET @int = @int+1
    END
  3. Greg Larsen New Member

    You need to put your variable inside of parenthesis like so:DECLARE
    @int INT SET
    @int = 4WHILE
    (@int)<10BEGIN
    SELECT
    TOP (@int) * FROM Pubs..salesSET
    @int = @int+1END
  4. jagblue New Member

    HI Sheetal
    It want work as TOP clause looks for Constant value and not Veriable
    the righr way run this is using dynamic SQL as Adriaan suggested
    While running it like this you need to make sure User running this procedure has select permission on tableor you will get permission error.
  5. Greg Larsen New Member

    In SQL Server 2000 I believe it is true you have to use dynamic SQL to accomplish your requirement. Can't exactly remember when SQL Server started allowed a variable in the TOP criteria. But, in SQL Server 2005 you can use what I suggested if you desire. Or at least it works fine for me on the sample code I provided below. When I ran this code I first got 4 records returned for the first pass through the WHILE loop, then 5 for the second pass, 6 for the third pass and so on.But I now see you where using the Pubs..sales table, which if I recall correctly is a SQL Server 2000 sample table from Microsoft. So potentially you need a SQL Server 2000 solution and not SQL Server 2005 solution as I proposed. Both the dynamic solution provided by Adriaan and the variable solution below will work in 2005. I’m guessing one might be more efficient then the other, but I didn’t do that analysis. use AdventureWorks goDECLARE
    @int INT SET
    @int = 4 WHILE
    (@int)<10BEGINSELECT TOP (@int) * FROM Sales.SalesOrderDetail

    SET @int = @int+1END
  6. FrankKalis Moderator

    Just to rule this out. The compatibility level of that database in your SQL Server 2005 instance is 90, correct? Just asking, because this is a popular cause for trouble when you move databases to 2005 and think that's it. [:)]
  7. Greg Larsen New Member

    Very good point!
  8. Madhivanan Moderator

    <P mce_keep="true">[quote user="FrankKalis"] <P>Just to rule this out. The compatibility level of that database in your SQL Server 2005 instance is 90, correct? Just asking, because this is a popular cause for trouble when you move databases to 2005 and think that's it. <IMG alt=Smile src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif"></P><P>[/quote]</P><P>Exactly it is </P><P>Sometimes back I also suffered from the same [:)]</P>
  9. praveen.battula New Member

    I believe you got answer. But, here is a working version for all SQL SERVER versions. Check it out/http://praveenbattula.blogspot.com/2011/01/using-variables-in-top-clause-in-t-sql.html
  10. Luis Martin Moderator

    Welcome to the forum Praveen.!!
    Please don't write in old thread.
    You can start a new one.
  11. Madhivanan Moderator

    [quote user="Luis Martin"]
    Welcome to the forum Praveen.!!
    Please don't write in old thread.
    You can start a new one.
    [/quote]
    First time posteres always post in the thread in which they know the answer regardless of when the thread was started. I have seen in various forums.
  12. FrankKalis Moderator

    But it seems to happen more frequently recently.
  13. yuanyelss New Member

    While running it like this you need to make sure User running this procedure has select permission on tableor you will get permission error...
  14. satya Moderator

    More or less this is a reference to their personal blog, in a way it is good to share the knowledge but not good if you are dusting off old thread. [:0}.

Share This Page