dynamic TOP query - 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 2005 Forums

Transact-SQL (2005)

dynamic TOP query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-12 : 13:20:05

Hi,

I have a few queries that are the same, but return a different amount of rows via the TOP function.

I've always just written a copy for each different TOP statement, for example 1 SP for TOP 10, and another SP for TOP 100 results.

Is there a better way to do this ? I don't want to impact performance, and impretty sure that means not getting involved in dynamic SQL.

Just wondering my options here, and if I should just continue what I am already doing.

Thanks very much!
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 13:26:34

you can use like this

CREATE PROC ReturnData
@Count int
AS
SELECT TOP (@Count) fields...
FROM....


GO

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-12 : 13:27:04

[code]
Declare @i int
set @i = 10

select top(@i) from <tab> order by <col>[/code]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-12 : 16:09:03

quote:
Originally posted by visakh16

you can use like this

CREATE PROC ReturnData
@Count int
AS
SELECT TOP (@Count) fields...
FROM....

GO

Hi Visakh16,

Would this possibly slow down performance in anyway ?

Thanks!
Mike123

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-12 : 16:23:51

no it wouldn't

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-12 : 17:06:14

awesome, time to consolidate some sprocs..

thank you! :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 23:20:34

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources