Home » T-SQL » How do I use a variable in a TOP clause in SQL Server 2000? 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 Sunday, January 13, 2008 by Viktar Karpach | Comments (4) | Add Comment Comments Re:How do I use a variable in a TOP clause in SQL Server 2000? 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 1/14/2011 8:58:57 PM by Praveen # Re:How do I use a variable in a TOP clause in SQL Server 2000? Having problem with SQL query in classic ASP with sql 2000 as databaseMy query is:<%------------------------------------------------------dim top,top1top =20top=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.Thankin you.My email id : dutta_devashish1@rediffmail.com Posted on 3/13/2011 5:22:05 AM by Devashish Dutta # Try to replace your nested query with left outer join. Test query in SQL Management Studio. Re:How do I use a variable in a TOP clause in SQL Server 2000? I am attempting to use this in VS2005 writing a Reporting Services report.However, I am receiving the error "The number of rows in a TOP clause must be an integer".The parameter within the report is set as an integer. It seems VS is sending the parameter value as something other than an integer?http://www.meratvforum.com Posted on 3/19/2011 9:33:08 PM by Waleed # Use sql profiler to see what Visual Studio passing. Re:How do I use a variable in a TOP clause in SQL Server 2000? check thishttp://cowburn.info/2007/03/15/select-top-parameter/ Posted on 5/12/2011 10:07:39 AM by hellosuresh # New Comment Your Name: Email (for internal use only): Comment: Code above: