views:

700

answers:

3
A: 

try
SELECT CASE LEN(@gcode)
WHEN IN(x, y, z) THEN a
etc.

or you may need
SELECT CASE LEN(@gcode)
WHEN x THEN a
WHEN y THEN a
etc.

Here's the reference.

le dorfier
I get the error ....incorrect syntax near the keyword IN
Saif Khan
+3  A: 

try this:

Select @Type = 
(select case 
WHEN len(@code) IN (12,14,17) THEN 1
WHEN len(@code) IN (13,15,18) THEN 2
WHEN len(@code) IN (8,10) THEN 3
ELSE  0
END)
keithwarren7
Thanks mate! I did this a long time back but couldn't remember...couldn't find on msdn either and also BOL.
Saif Khan
take a look at Dave Markle's solution below - he uses the same case syntax but tightened up the syntax of the function itself
keithwarren7
+5  A: 

This should do it:

CREATE FUNCTION getItemType(@code VARCHAR(18))
RETURNS INT
AS
BEGIN
    RETURN CASE 
     WHEN LEN(@code) IN (12,14,17) THEN 1
     WHEN LEN(@code) IN (13,15,18) THEN 2
     WHEN LEN(@code) IN (8,100)    THEN 3
     ELSE  0
    END
END
Dave Markle