SQL CASE Statement

Hi, Is the following possible in SQL Server 2000?

CREATE FUNCTION getItemType  (@code varchar(18))
RETURNS int
AS
BEGIN
Declare @Type tinyint
Select @Type = case len(@code)
WHEN 12,14,17 THEN 1
WHEN 13,15,18 THEN 2
WHEN 8,10 THEN 3
ELSE  0
END
RETURN (@Type)
END

Thanks

From stackoverflow

• 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.

  Saif Khan : I get the error ....incorrect syntax near the keyword IN

• 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)
  

  Saif Khan : Thanks mate! I did this a long time back but couldn't remember...couldn't find on msdn either and also BOL.

  keithwarren7 : take a look at Dave Markle's solution below - he uses the same case syntax but tightened up the syntax of the function itself

• 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