KARPACH

WEB DEVELOPER BLOG

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

Comments

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.

Posted on 3/19/2011 09:33:08 PM by Waleed

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

Use sql profiler to see what Visual Studio passing.