Conditional Where Clause - 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)

Conditional Where Clause

Author

Topic

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-03 : 03:08:09

Hi All,

I have 1 SP, which has Dynamic SQL Statement. Now I have to update this to Static SQL. Upto certain level I have completed it. In a Where clause of this qry I have applied conditions like:

WHERE @status = (Case When @status = 'ALL' Then LEFT(iTSBase.iStatus,2) <> 'AR' Else iTSBase.iStatus = @status End)
AND @CreatedBy = (Case When @CreatedBy = 'M' Then CreatorName Is Null
When @createdBy = 'O' Then CreatorName is Not NULL End)

it showing me error like :
Server: Msg 170, Level 15, State 1, Procedure nsp_TS_timesheetList_Mahesh, Line 59
Line 59: Incorrect syntax near '<'.

can anybody tell me, how to apply such conditions in where clause?

thanks in advance,

Mahesh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-03 : 03:17:58

[code]Where
((@status = 'ALL' And LEFT(iTSBase.iStatus,2) <> 'AR') OR
(@status <> 'ALL' And iTSBase.iStatus = @status)) AND
((@CreatedBy = 'M' And CreatorName Is Null) OR
(@createdBy = 'O' And CreatorName is Not NULL))[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-03 : 03:20:33

More optimized:

Where
((@status = 'ALL' And iTSBase.iStatus not like 'AR%') OR
 (@status <> 'ALL' And iTSBase.iStatus = @status)) AND
((@CreatedBy = 'M' And CreatorName Is Null) OR
 (@createdBy = 'O' And CreatorName is Not NULL))

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-03 : 03:47:46

Hi Harsh,

thanks for replying. but its not working well. can u suggest me another way to overcome the situation?

Thanks again,

Mahesh

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-03 : 04:05:22

give some sample data and desired output u want

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-04 : 04:09:14

Also post your full dynamic sql

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-04 : 06:57:47

in dynamic sql you have to replace 1 single quote ' with 2 single quotes ''

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-04 : 07:32:53

Also, based on what you were trying, you do not understand how CASE works; plase see: http://weblogs.sqlteam.com/jeffs/archive/2007/05/03/60195.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources