filtering data or not filtering, that's my quest..

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

SQL Server Development (2000)

filtering data or not filtering, that's my quest..

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2009-09-09 : 11:38:16

Hi all,

I'm writing a query and I've set a parameter to, for example:
SET @user = 'whatever name'

I have a varchar column in my query that containcs names. First of all, I want to filter the results based on the match between the parameter and any rows where the name matches. However, I also want to show all rows if there is no match in any of the rows.

The problem is that in the real world a parameter will be passed that will either have a name that matches some of the rows or a parameter that wont. It will never be null, it will always be something and if it doesn't match, it could be anything. These are the conditions.

Is there a way to do this whereby if SQL server finds no matches in any of the rows it will return everything regardless of the parameter?

Many thanks in advance for the help

P

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-09 : 12:01:16

if exists (select * from table where colxy = @user)
begin
select * from table where colxy = @user
end
else
begin
select * from table
end

No, you're never too old to Yak'n'Roll if you're too young to die.

cidr
Posting Yak Master

207 Posts

Posted - 2009-09-10 : 07:26:36

Thanks for responding webfred, this certainly works so I appreciate that.

I was just wondering, would this be the easiest way to do this or do you know of other ways? The only reason I ask is that is because the query is very long and I'd be repeating it for the else condition.

Thanks again

P

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-10 : 08:15:12

No other way. You need to repeat the sql

Madhivanan

Failing to plan is Planning to fail

cidr
Posting Yak Master

207 Posts

Posted - 2009-09-10 : 09:18:03

thanks for that...

p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources