Case statement in SQL

Question

Post reply

Case statement in SQL

sachinrshetty

SSCrazy

Points: 2062
More actions
April 23, 2010 at 6:04 am

#138706

IF(@ZLV_APPLN_ID IS NULL)
BEGIN
SET @QUERY = CASE @ZLV_ID
WHEN 10 THEN 'UPDATE #TEMP_CUR_TBL SET CAS_L = 3 WHERE EMP_ID = ''' + @ZEMP_ID + ''''
WHEN 11 THEN 'UPDATE #TEMP_CUR_TBL SET EARN_L = 3 WHERE EMP_ID = ''' + @ZEMP_ID + ''''
WHEN 15 THEN 'UPDATE #TEMP_CUR_TBL SET PAREN_L = 3 WHERE EMP_ID = ''' + @ZEMP_ID + ''''
WHEN 13 THEN 'UPDATE #TEMP_CUR_TBL SET MARTRN_LV = 3 WHERE EMP_ID = ''' + @ZEMP_ID + ''''
WHEN 16 THEN 'UPDATE #TEMP_CUR_TBL SET BERV_LV = 3 WHERE EMP_ID = ''' + @ZEMP_ID + ''''
END
EXEC(@QUERY)
END
The above code i have written in Stored Procedure. but I m getting error as below
Msg 8115, Level 16, State 6, Procedure leave_balance_details, Line 39
Arithmetic overflow error converting varchar to data type numeric.
Msg 8115, Level 16, State 6, Procedure leave_balance_details, Line 39
Arithmetic overflow error converting varchar to data type numeric.
Please help me to solve the issue.
Thank u all

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

this one is just a basic error, no worries.

the issue is this part i think:

WHERE EMP_ID = ''' + @ZEMP_ID + ''''

you are doing VARCHAR + INT, which results in the error; SQL will not implicitly convert the int/decimal/numeric/float on your behalf.

when concatenating an into to an existing string, you must convert it to varchar or nvarchar:

WHERE EMP_ID = ''' + CONVERT(varchar,@ZEMP_ID) + ''''

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sachinrshetty SSCrazy Points: 2062 More actions · Answer 2

Thank u Lowell Thanks a lot man.

It worked fine. Big headache i was trying to solve this from afternoon 🙂

Thank u very much.

This is 2nd time you are helping me.

Thanks for all your help