Dynamic 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 2005 Forums

Transact-SQL (2005)

Dynamic Top

Author

Topic

nduggan23
Starting Member

42 Posts

Posted - 2007-11-28 : 07:59:05

Hi all,

Have a slight problem.


CREATE PROCEDURE [dbo].[sp_viewItemsCheckedOut]
(
	@ItemCount int = NULL
) 
AS
SET NOCOUNT ON;
Select TOP (@ItemCount) * from tblItems
END;

This works fine for when a user enters a number for ItemCount. But i want to be able to use the same procedure to return all rows say if 0 is entered.

What is the best way of going about this?

N.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-28 : 08:01:59

can you use IF statement to do it ?

KH
[spoiler]Time is always against us[/spoiler]

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-28 : 08:02:33

I would say put it in a IF and run whichever depending on the variable.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-28 : 08:10:38

CREATE PROCEDURE [dbo].[sp_viewItemsCheckedOut]
(
@ItemCount int = NULL
)
AS
SET NOCOUNT ON;
SET ROWCOUNT @ItemCount
Select * from tblItems
END;

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-28 : 08:11:28

or use set rowcount

CREATE PROCEDURE [dbo].[sp_viewItemsCheckedOut]
(
	@ItemCount int = NULL
) 
AS
SET NOCOUNT ON
SET ROWCOUNT @ItemCount
Select * from tblItems
SET ROWCOUNT 0
END

But I would say you should use order by to make it more sense

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-28 : 08:12:21

Madhivanan

Failing to plan is Planning to fail

nduggan23
Starting Member

42 Posts

Posted - 2007-11-28 : 08:55:17

Cheers ppl. rowcount it is with an if statement then.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-28 : 08:58:01

quote:
Originally posted by nduggan23

Cheers ppl. rowcount it is with an if statement then.

You dont need IF if you used rowcount
Can you post what you use now?

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-28 : 08:58:11

you don't need an if at all.
set rowcount 0 means all rows

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-28 : 08:58:26

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-28 : 08:59:57

I suggest to use IF and TOP, SELECT with TOP can generate a different exec plan then a simple SELECT, that is why Microsoft recommends to use TOP instead of SET ROWCOUNT

nduggan23
Starting Member

42 Posts

Posted - 2007-11-28 : 09:12:56

[code]CREATE PROCEDURE [dbo].[sp_viewItemsCheckedOut]
(
@ItemCount int = NULL
)
AS
SET NOCOUNT ON
IF (@ItemCount = 0)
BEGIN
Select id, title, type from tblItems order by id
END
ELSE
BEGIN
Select TOP (@ItemCount) id, title, type from tblItems order by id
END

CREATE PROCEDURE [dbo].[sp_viewItemsCheckedOut]
(
@ItemCount int = NULL
)
AS
SET NOCOUNT ON
BEGIN
SET ROWCOUNT @ItemCount
Select id, title, type from tblItems order by id
SET ROWCOUNT 0
END

[\code]

Two methods. both seem to work...

nduggan23
Starting Member

42 Posts

Posted - 2007-11-28 : 09:23:19

Suppose the next question is which is more efficient? largest result 100,000 rows filtered down to 10 rows. thats the outside load i would expect returned.

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-28 : 09:37:12

Read my post, first one is better

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-29 : 01:35:40

quote:
Originally posted by nduggan23

Suppose the next question is which is more efficient? largest result 100,000 rows filtered down to 10 rows. thats the outside load i would expect returned.

Set the execution plan and see

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources