top problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

top problems

Author

Topic

spock
Starting Member

35 Posts

Posted - 2002-01-24 : 05:35:44

hi ,

DECLARE @IN INT
SET @IN = 50

SELECT * FROM
(SELECT TOP @IN * FROM EMPT_EMP) A
LEFT OUTER JOIN
(SELECT TOP (@IN - 10) * FROM EMPT_EMP) B
ON A.EMP_ID = B.EMP_ID AND A.ORGN_ID = B.ORGN_ID
WHERE B.EMP_ID IS NULL

this query does not work when i use a variable @in but works when i code specific values (like 50,40) instead of @in. Without building a dynamic sql query and executing it , is it possible to do this ?

Thanks
kaushik

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-01-24 : 06:07:53

I'm fairly sure you can't use a variable within the TOP statement. Search this site for TOP and you'll find a wealth of information on it.

spock
Starting Member

35 Posts

Posted - 2002-01-24 : 07:08:44

well to be clear what i am trying to do is simulate ado paging in SQL Server itself because then i would not have to move huge amts of data from db to IIS and then do paging at the web server.i came across one of the scripts in sqlteam wherein a temporary table was created and from that the same was acheived. but was just wondering if i could do it without temporary tables.

Thanks
kaushik

Nazim
A custom title

1408 Posts

Posted - 2002-01-24 : 07:48:46

you can have a look at these articles for paging
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=6230
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=6180

Check this link too. i thought it should help you.

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=11716

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2002-01-24 : 07:54:49

spock,

G'day, it's do-able, use
...
SET ROWCOUNT @IN
(SELECT * FROM EMPT_EMP) A
...

SET ROWCOUNT 0

that should select you the top 50..

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2002-01-24 : 07:58:27

DANG!! just as good as being sniped!!! BY 3 seconds!! GRRRR...

Oh, I tried!!!

must be the lateness!!!

BY THE WAY I JUST TURNED 28...

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman

spock
Starting Member

35 Posts

Posted - 2002-01-24 : 08:08:04

nazim,

thanks for the links. unfortunately i think i cant solve the problem without using temporary tables/dynamic sql. any ideas on doing it with a single select query ?

jake,
thanks m8 but i need to get the first 50 records for the first sub query and the first 40 records for the next sub query and i cant change the row count in between.

thanks
kaushik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources