asked on

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,

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

66chawger

ASKER

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?

66chawger

ASKER

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

66chawger

ASKER

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.