Dynamically applying TOP - 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)

Dynamically applying TOP

Author

Topic

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-10-05 : 04:35:21

I have one sql statemnet in which i use TOP. i.e.

SELECT TOP 4 FROM <TABLE NAME> WHERE <CONDITION> ORDER BY <DESC>

now my question is, can i add dynamically number to the statement instead of using such hard coded values. i.e.

SELECT TOP @Counter FROM <TABLE NAME> WHERE <CONDITION> ORDER BY <DESC>

is it possible?

thanks in advance,

Mahesh

Kristen
Test

22859 Posts

Posted - 2006-10-05 : 04:37:56

[code]SET ROWCOUNT @Counter
SELECT MyColumn
FROM <TABLE NAME> WHERE <CONDITION> ORDER BY <DESC>
SET ROWCOUNT 0 -- Reset - very important!!
[/code]
Kristen

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-10-05 : 04:45:12

thanks Kristen,

actually i m using this sql statement in one SP which has two select statments. 1st statement returns 10 and second returns more than 200 records. now i want to set the TOP value depending upon the master table. fot that i had used:

...
DECLARE @Counter NUMERIC
SELECT @Counter = COUNT(ID) FROM <table>

--my 1st select statement
SELECT

--my 2ND select statement
SELECT
...

in the second select statment i have one subquery, in which i wants to apply the value of @Counter to the TOP.

how to apply dynamically value to the TOP in such way?

thanks again.
Mahesh

Kristen
Test

22859 Posts

Posted - 2006-10-05 : 04:58:45

Well I expect you don't want to use Dynamic SQL (if by some chance you don;t know what I mean just ask)

I expect you will have to drag the inner subquary into a Temp Table (using the SET ROWCOUNT trick), and then JOIN that into the original query

Or you could just find what the First and Last records would be in the subquery (i.e. select the MIN/MAX column value, by the ORDER BY, into @variables and then use those in the subquery as a constraint.

Its harder to explain than to give an example! Jeff uses these in a rather cute paging method:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Efficient+paging+of+recordsets

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-05 : 05:18:07

Are your master table and the table which you are using in the Sub Query is linked to each other in any way??

Then you make a join and fetch the number of records matches the count

Chirag

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-05 : 11:44:52

http://www.aspfaq.com/show.asp?id=2120

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources