Using an input parameter for Top or Rowcount

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)

Using an input parameter for Top or Rowcount

Author

Topic

KelCook
Starting Member

5 Posts

Posted - 2004-09-22 : 15:33:08

I am running a query in which users have requested to return either the top 100, 250, or 500 results.
How can I make this an input parameter? I have been using @results but cannot get the syntax to work.
For example... "Select Top (insert @results here if possible) Title, Author" ...etc.

Thanks in advance for any help!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 15:35:10

well you can use rowcount:

declare @i int
set @i = 100
set rowcount @i
select * from MyTable
set rowcount 0

or dynamic sql...

Go with the flow & have fun! Else fight the flow

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-22 : 15:36:24

This will require dynamic sql, which is not recommended. You could use SET ROWCOUNT instead.

Declare @int int

set @int = 10

set rowcount @int

select *
from yourtable

set rowcount 0

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-22 : 15:37:09

We're not elaborating on dynamic sql for a reason.

Tara

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-22 : 16:37:31

I was wondering whats the point of using TOP if set rowcount does the exact same thing. Is there a performance issue?

Thanks.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 16:40:35

see:

http://www.microsoft.com/sql/techinfo/tips/development/May21.asp

and with rowcout you can't use percent like in top.

Go with the flow & have fun! Else fight the flow

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-22 : 16:42:39

Thanks for the link. I've always used the TOP keyword and never used the SET ROWCOUNT command so I was a little curious.

Dustin Michaels

Kristen
Test

22859 Posts

Posted - 2004-09-23 : 04:43:25

SET ROWCOUNT effects all subsequent operations until a SET ROWCOUNT 0 - so it can muck up triggers, and things like that

SET ROWCOUNT 50
INSERT INTO MyTable
SELECT *
FROM MyOtherTable

where MyTable has a trigger that copies all the "children" of the rows - i.e. more than 50 rows.

Kristen

KelCook
Starting Member

5 Posts

Posted - 2004-09-23 : 10:49:55

Thanks everyone for your help! Seems to be working. However, can anyone tell me if I can use an input parameter for the "order by" clause? I am not having any luck and would prefer to NOT use dynamic sequel if at all possible.
Thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-23 : 10:58:46

not that i know of...

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-23 : 11:00:13

search the forums and articles here for "dynamic order by" -- you should find tons of examples. it all depends on your specific needs.

- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-23 : 11:17:21

to help him:
http://www.sqlteam.com/item.asp?ItemID=2209

<BangOnTheHead> i totally forgot that you can put case in order by...</BangOnTheHead>

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-23 : 11:30:00

there's a lot of comments in that article, but make sure you browse through them because they address issues like choosing ASC/DESC dynamically and problems with mixing datatypes in the CASE expression, and how to avoid them.

- Jeff

KelCook
Starting Member

5 Posts

Posted - 2004-09-23 : 11:33:28

You peeps are so quick! Thank you so much.
I think I found what I am looking for and will be working on it all afternoon. All the help and speed is appreciated!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources