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 | Comments (4) | Add Comment

Comments

Gravatar

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 #
Gravatar

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 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.

Thankin you.
My email id : dutta_devashish1@rediffmail.com

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

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

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 #
Gravatar

Use sql profiler to see what Visual Studio passing.
Gravatar

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

check this
http://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:

Categories

Valid HTML5