select top question - 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)

select top question

Author

Topic

chih
Posting Yak Master

154 Posts

Posted - 2008-06-23 : 01:08:04

Hi everyone,

I want to use select top(@variable) statement. there are 2 conditions,
if @in is null then display all records
otherwise only display top (@variable) records

Is that possible, I can use only one statement
'select top(@variable) * from staff' to get wahat i want?

Thank you in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-23 : 01:18:43

use IF to check


if	@variableis null 
	select * from staff
else 	
	select top (@variable) * from staff

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 01:19:56

[code]DECLARE @n int,@cnt int

SELECT @cnt=COUNT(*)
FROM Staff

SET @n=10 (say)

SELECT TOP (coalesce(@n,@cnt)) * FROM Staff[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-23 : 01:24:23

quote:

Originally posted by visakh16

DECLARE @n int,@cnt int

SELECT @cnt=COUNT(*)
FROM Staff

SET @n=10 (say)

SELECT TOP (coalesce(@n,@cnt)) * FROM Staff

the select count(*) will be an constant over head even if @n is 10. For situation where @n is NULL, the additional TOP clause will also introduce overhead to the query compare to just plain select *

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources