How do I use a variable in a TOP clause in SQL Server 2000?

CREATE PROCEDURE dbo.getFoo
    @top INT
AS
BEGIN
    SET ROWCOUNT @top 

    SELECT foo
    FROM blat
    ORDER BY foo DESC

    -- never forget to set it back to 0!
    SET ROWCOUNT 0
END
GO

Posted on January 13, 2008 by Viktar Karpach

Comments

Posted on 1/14/2011 08:58:57 PM by Praveen

Good one. Added this to my blogpost. Take a look at this post.

http://praveenbattula.blogspot.com/2011/01/using-variables-in-top-clause-in-t-sql.html

Posted on 3/13/2011 05:22:05 AM by Devashish Dutta

Having problem with SQL query in classic ASP with sql 2000 as database

My query is:

dim top,top1
top =20
top=30

set rs=cn.Execute("Select TOP”& top &” box_no,slno1,name,f_name,address1,mobile_no,dist,slno from prepaidclient_data WHERE (slno NOT IN (SELECT top” & top1 & “slno,SLNO1 FROM  prepaidclient_data ORDER BY slno)) and dist='"&a&"' ORDER BY slno,box_no")

Error Type:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

[Microsoft][ODBC SQL Server Driver][SQL Server]Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Thank you.

My email id : dutta_devashish1@rediffmail.com

Try to replace your nested query with left outer join. Test query in SQL Management Studio.