Case between -help - 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 2008 Forums

Transact-SQL (2008)

Case between -help

Author

Topic

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-08-06 : 07:19:42

This doesnt work ...what is wrong?


And (case when @FinsoftDepartmentCodeR0 <> '' and @FinsoftDepartmentCodeR1 <> ''   then Department.CustomProperty01 between @FinsoftDepartmentCodeR0 AND @FinsoftDepartmentCodeR1  Else Department.CustomProperty01 =Department.CustomProperty01 End  )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 10:10:43

case is an expression not a control flow statement so you cant have conditions inside case. it should be like


AND (Department.CustomProperty01 between @FinsoftDepartmentCodeR0 AND @FinsoftDepartmentCodeR1  
OR (@FinsoftDepartmentCodeR0 = '' and @FinsoftDepartmentCodeR1 = ''   ))

..

i didnt understand reason for below condition

Department.CustomProperty01 =Department.CustomProperty01

as it looks trivial

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-08-06 : 10:44:19

quote:
Originally posted by visakh16

case is an expression not a control flow statement so you cant have conditions inside case. it should be like

AND (Department.CustomProperty01 between @FinsoftDepartmentCodeR0 AND @FinsoftDepartmentCodeR1
OR (@FinsoftDepartmentCodeR0 = '' and @FinsoftDepartmentCodeR1 = '' ))

..

i didnt understand reason for below condition

Department.CustomProperty01 =Department.CustomProperty01

as it looks trivial

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

@FinsoftDepartmentCodeR1 and @FinsoftDepartmentCodeR0 are optional so they may be null..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 10:53:50

then add that too


AND (Department.CustomProperty01 between @FinsoftDepartmentCodeR0 AND @FinsoftDepartmentCodeR1  
OR (NULLIF(@FinsoftDepartmentCodeR0,'') IS NULL and NULLIF(@FinsoftDepartmentCodeR1,'') IS NULL   ))

you still didnt explain reason for other condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-08-07 : 02:29:12

quote:
Originally posted by visakh16

then add that too

AND (Department.CustomProperty01 between @FinsoftDepartmentCodeR0 AND @FinsoftDepartmentCodeR1
OR (NULLIF(@FinsoftDepartmentCodeR0,'') IS NULL and NULLIF(@FinsoftDepartmentCodeR1,'') IS NULL ))

you still didnt explain reason for other condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Basically, since {SET @FinsoftDepartmentCodeR0 ='1' ---1, SET @FinsoftDepartmentCodeR1 ='200' ---200 } can be null I want to search all the records regardless of a range. Namely concept of (1=1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 10:01:24

the OR will take care of NULL values condition. you dont require that additional 1=1 condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources