General - 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 2000 Forums

Transact-SQL (2000)

General

Author

Topic

baska123
Yak Posting Veteran

64 Posts

Posted - 2006-09-08 : 12:41:14

I have this where clause that depending on the situation I want to use different 'and' statement. How can I do it?

where a.AssignmentTimeActualWork IS not NULL
and a.AssignmentTimeActualWork <>0
and AssignmentTimeStart >= @StartDate
and AssignmentTimeStart <= @EndDate
this is my choice. I want to use this one
and b.wres_id_mgr = @ResourceID
or I want to use
and b.wres_id In (Select ID From fnSplitter(@IDs))

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-08 : 12:52:05

I would use conditional logic...how do you know when you'd like to use one over the other?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2006-09-08 : 13:02:45

Most commonly I see thie done passing NULL for "don't care" parameters:


and (@ResourceID IS NULL OR b.wres_id_mgr = @ResourceID)
and (@IDs IS NULL OR  b.wres_id In (Select ID From fnSplitter(@IDs)))

Kristen

baska123
Yak Posting Veteran

64 Posts

Posted - 2006-09-08 : 13:17:12

My condition should be

If @ResourceID = 'All Resources then'
and b.wres_id In (Select ID From fnSplitter(@IDs))
else
and b.wres_id_mgr = @ResourceID

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-08 : 13:46:47


IF @ResourceID = 'All Resources then'
	....query1
  ELSE
	....query2

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

baska123
Yak Posting Veteran

64 Posts

Posted - 2006-09-08 : 15:32:09

Is it possible to have if esle statements in the middle of sql. For example:

Select name, l_name
from names
where
age = 25
and gender = F
if @ResourceID = All Resources
begin
and b.wres_id_mgr = @ResourceID
end
else
and b.wres_id In (Select ID From fnSplitter(@IDs)

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-08 : 15:42:36

no

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2006-09-09 : 03:59:41

No, as Brett said, but its can easily be expressed as just another AND with a nested OR ...

Kristen

baska123
Yak Posting Veteran

64 Posts

Posted - 2006-09-11 : 15:58:09

What do you mean Kristen?

Kristen
Test

22859 Posts

Posted - 2006-09-12 : 02:34:57

"What do you mean Kristen?"

Well

where
age = 25
and gender = F
if @ResourceID = All Resources
begin
and b.wres_id_mgr = @ResourceID
end
else
and b.wres_id In (Select ID From fnSplitter(@IDs)

is the same as being expressed as:


where
age = 25 
and gender = F
AND (
	(
		    @ResourceID = All Resources
		and b.wres_id_mgr = @ResourceID
	)
	OR b.wres_id In (Select ID From fnSplitter(@IDs)
)

isn't it?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources