Dynamically using 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

Transact-SQL (2000)

Dynamically using top

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-25 : 02:44:35

DECLARE @top_number INT
SET @top_number=100

SELECT top @top_number emp_number from tbl_active_emp_number

How can I use top 100 dynamically in this case ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-25 : 02:50:43

In SQL 2005 you can. But TOP keyword has really no meaning unless you also specify an ORDER BY.
In SQL 2000 you can emulate this behaviour by writing

SELECT ae.* FROM tbl_active_emp ae where (select count(*) from tbl_active_emp x where x.somcol <= ae.somecol) <= @top_number

Peter Larsson
Helsingborg, Sweden

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-10-25 : 02:54:14

What you write works with 2005, but you have top put @top_number in parenteses

SELECT TOP (@top_number) emp_number from tbl_active_emp_number

, else read http://www.sqlteam.com/item.asp?ItemID=233

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-25 : 05:55:17

SET ROWCOUNT @top_number
SELECT emp_number from tbl_active_emp_number Order by somecol
SET ROWCOUNT 0 --This is important to reset to 0

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources