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)
 To Many Case in Stored Procedure (Case expressions

Author  Topic 

ismailc
Constraint Violating Yak Guru

290 Posts
Posted - 2010-02-23 : 08:22:12
Good day,

I have a sql that runs fine in my normal db, buty i now have to create a stored procedure which i use in my SSRS report.

It is a very lengthy query which tracks all activity within DB, the query can't be shortened anymore.


Error when creating Stored proc: Case expressions may only be nested to level 10.

Report cant go live without stored procedure

Anyway i can get pass this, Please help.

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-02-23 : 08:33:31
Without seeing the statement it is not possible to help.
Maybe there is no need for nesting?

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

ismailc
Constraint Violating Yak Guru

290 Posts
Posted - 2010-02-23 : 08:52:06
Hi, The query has 574 lines
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-02-23 : 08:58:18
Maybe you can show a partial of "case when then end"?

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

ismailc
Constraint Violating Yak Guru

290 Posts
Posted - 2010-02-23 : 09:15:59
Hi,

I had a look at the if else but would it work for, please look below example
show how to use, Regards

,Ctrl.Displayname as DisplayName
,case when Val.ControlValue is null then ''
when Val.ControlID = 'VE1020User' then Val.ControlValue
when Val.ControlID = 'VE10NatSite' then Val.ControlValue
when Val.ControlID = 'VE1055VendorsLK' then Val.ControlValue
when Val.ControlID = 'VE1060NewSupplier' then Val.ControlValue
when Val.ControlID = 'VE1077Doc' and Val.ControlValue = 'True' then 'Yes'
when Val.ControlID = 'VE1090INTCreditApp' and Val.ControlValue <> '' then 'Attached'
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-02-23 : 09:35:46
There I can't see a nesting.
Is there a part in your coding where is a case inside a case inside a case?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-23 : 09:41:35
quote:
Originally posted by ismailc

Hi,

I had a look at the if else but would it work for, please look below example
show how to use, Regards

,Ctrl.Displayname as DisplayName
,case when Val.ControlValue is null then ''
when Val.ControlID = 'VE1020User' then Val.ControlValue
when Val.ControlID = 'VE10NatSite' then Val.ControlValue
when Val.ControlID = 'VE1055VendorsLK' then Val.ControlValue
when Val.ControlID = 'VE1060NewSupplier' then Val.ControlValue
when Val.ControlID = 'VE1077Doc' and Val.ControlValue = 'True' then 'Yes'
when Val.ControlID = 'VE1090INTCreditApp' and Val.ControlValue <> '' then 'Attached'


you dont need to use separate when if return value is same. so above will be same as


,Ctrl.Displayname as DisplayName
,case when Val.ControlValue is null then '' 
        when Val.ControlID = 'VE1020User' 
             Or Val.ControlID = 'VE10NatSite' 
             Or Val.ControlID = 'VE1055VendorsLK' 
             Or Val.ControlID = 'VE1060NewSupplier' 
        then Val.ControlValue
        when Val.ControlID = 'VE1077Doc' and Val.ControlValue = 'True' then 'Yes'
        when Val.ControlID = 'VE1090INTCreditApp' and Val.ControlValue <> '' then 'Attached'



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-02-23 : 09:43:58
But I thought that is no nesting?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-23 : 09:55:35
quote:
Originally posted by webfred

But I thought that is no nesting?

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

I also never told there's nesting . I just showed another way eliminating need for repeating each cases uses when

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-02-23 : 10:10:49
Sure

But I thought about because of the original post:
"Error when creating Stored proc: Case expressions may only be nested to level 10."

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2010-02-23 : 10:12:57
unless he's never ended them?


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

ismailc
Constraint Violating Yak Guru

290 Posts
Posted - 2010-02-23 : 14:46:32
Thank You All, in a real mess

I have multiple Sub Selects, below is one
Does the error mean i can not have more than 10 when in a case
Case expressions may only be nested to level 10.

here is another one of the sub selects:
,case when E3.EventID in ('95','123','137','144')then 'IniT'
when E3.EventID in ('97','124','138') then 'Finance'
when E3.EventID in ('98','125','139','145','147') then 'MFS'
when E3.EventID = '99' then 'R&D'
when E3.EventID in ('100','126','140') then 'Costing'
when E3.EventID = '101' then 'Marketing'
when E3.EventID = '150' then 'Exports'
when E3.EventID = '151' then 'Procurement'
when E3.EventID in ('104','141') then 'Master Files 2'
when E3.EventID = '153' then 'Initiate'
when E3.EventID = '158' then 'DC Administrator'

What to do?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-02-24 : 02:29:53
quote:
Originally posted by ismailc

Thank You All, in a real mess

I have multiple Sub Selects, below is one
Does the error mean i can not have more than 10 when in a case
Case expressions may only be nested to level 10.

here is another one of the sub selects:
,case when E3.EventID in ('95','123','137','144')then 'IniT'
when E3.EventID in ('97','124','138') then 'Finance'
when E3.EventID in ('98','125','139','145','147') then 'MFS'
when E3.EventID = '99' then 'R&D'
when E3.EventID in ('100','126','140') then 'Costing'
when E3.EventID = '101' then 'Marketing'
when E3.EventID = '150' then 'Exports'
when E3.EventID = '151' then 'Procurement'
when E3.EventID in ('104','141') then 'Master Files 2'
when E3.EventID = '153' then 'Initiate'
when E3.EventID = '158' then 'DC Administrator'

What to do?

You dont understand the point
Do you have something like this?

case when <> then
     case when <> then
        case when <> then <> else <> end
     else
         case when <> else 
                      case when <> then <> else <> end
.
.
.
.end



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site