SQL 2005 Stored Procedure Select with Case Statement issue

I have the following TSQL in my stored procedure for case statement withing a select.  The stored proc itself is an insert into using select.

My issue is I am getting a "Invalid length parameter passed to substring function".... but with WHEN logic, why is the Substring being challenged???... The THEN statement should be processed as the @EventTypeCd does = 'EI'

I have changed the case statement to use WHEN ELSE and just WHEN's.   All of which result in the error mentioned above.  

Employee_First_Nm = case
                 When @EventTypeCd = 'EI'
                    Then (select em.First_Name_Display_Tx from vw_HSRS_People em where em.AD_Account_Tx = @sEmployee)
                 Else (SUBSTRING(@sEmployeeCd, CHARINDEX(',', @sEmployeeCd)  + 1, LEN(@sEmployeeCd))) End,
            Employee_Last_Nm = Case
                 When  @EventTypeCd = 'EI'
                    Then (select em.Last_Name_Tx from vw_HSRS_People em where em.AD_Account_Tx = @sEmployee)
                Else (SUBSTRING(@sEmployeeCd, 0, CHARINDEX(',', @sEmployeeCd))) End,
66chawgerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
I see you opened a new similar question about exact same issue...did the trick to "print" the text helped to see if it goes on the ELSE/SUBSTRING branch instead of the expected WHEN branch?

Can you post the entire SP if possible? - clear any confidential info before posting.
Did you checked all other variables used in this and any other case from the SP body to NOT be NULL? I mean you can fill them up with empty string like ='' (two single quotes) instead of leaving them null

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
66chawgerAuthor Commented:
lcohan... sorry about that... I closed the other by mistake.

I found the issue.... the case statements listed above and in the original question were fine.  

I went further up in the SP and found this:

SET NOCOUNT ON

DECLARE @OrigEventType varchar(2)
DECLARE @sEmployeeCd VARCHAR(50)
set @OrigEventType = ''
set @sEmployeeCd = @sEmployee
set @sEmployeeCd = LEFT(@sEmployeeCd, CHARINDEX('-', @sEmployeeCd) - 1)

It was bombing on that last statement...so the issue is that this situation the @sEmployeeCd does not contain the '-'.   It considers this a substring function w/o being implied implicitly.  

So.... I need to put a condition around this set statement checking for the existence of the '-'... or can I do this within the statement itself?
66chawgerAuthor Commented:
Lochan,

This is what I was able to do last week to resolve my issue.  As stated in my previous post, the error was actually further up in the SP.

Changed
set @sEmployeeCd = LEFT(@sEmployeeCd, CHARINDEX('-', @sEmployeeCd) - 1)

To
set @sEmployeeCd = LEFT(@sEmployeeCd, LEN(@sEmployeeCd) - CHARINDEX('-', reverse(@sEmployeeCd)))

In my original question that you assisted with, you mentioned the "LEN" missing from the set statement.   I awarded you points for the last question (I closed by mistake), and you can have these points as well as after I thought about it, your comment about the "LEN" led me to look elsewhere in the SP.  Thanks
66chawgerAuthor Commented:
I had closed out my original question by mistake.  Expert did receive credit, however when I opened new question Expert picked right back up which I appreciate.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.