Select Top (Variable) doesn't work

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

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

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

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.

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

Very good point!

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

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