T-SQL Case Function

Question

I'm trying to write a function for our SQL Server 2000 that I can use in other stored procedures to clean up our machine ID numbers:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Description: The Purpose here is to just get the System (Chamber, Decay, Label, Tubing, etc.)
-- =============================================
CREATE FUNCTION [dbo].[fn_GetSysType] (@systemID varchar(50)) RETURNS varchar(50) AS
BEGIN
  declare @sysID varchar(50)
  set @sysID=LTrim(RTrim(@systemID))
  if (0<Len(@sysID)) begin
    set @sysID=(
      case when (0<CharIndex('Inspect', @sysID)) then 'Inspect'
*     case when (0<CharIndex('Label', @sysID)) then 'Label'
      case when (0<CharIndex('Tubing', @sysID)) then 'Tubing'
      case when (0<CharIndex('Expand', @sysID)) then 'Expand'
      case when (0<CharIndex('Decay', @sysID)) then 'Decay'
      case when (0<CharIndex('Chamber', @sysID)) then 'Chamber'
      case when (0<CharIndex('Water', @sysID)) then 'Water'
      case when (0<CharIndex('Sniff', @sysID)) then 'Sniff'
      case when (0<CharIndex('Packout', @sysID)) then 'Packout'
      case when (0<CharIndex('Shipping', @sysID)) then 'Shipping'
      else 'Unknown' end)
  end
  return @sysID
END
GO

The error message when I try to save this is:

Incorrect syntax near the keyword 'case'.

at the asterisk above.

What did I miss?

Answer 1

You just need to use CASE once.

...
set @sysID=(
  case when (0<CharIndex('Inspect', @sysID)) then 'Inspect'
       when (0<CharIndex('Label', @sysID)) then 'Label'
       when (0<CharIndex('Tubing', @sysID)) then 'Tubing'
       when (0<CharIndex('Expand', @sysID)) then 'Expand'
       when (0<CharIndex('Decay', @sysID)) then 'Decay'
       when (0<CharIndex('Chamber', @sysID)) then 'Chamber'
       when (0<CharIndex('Water', @sysID)) then 'Water'
       when (0<CharIndex('Sniff', @sysID)) then 'Sniff'
       when (0<CharIndex('Packout', @sysID)) then 'Packout'
       when (0<CharIndex('Shipping', @sysID)) then 'Shipping'
  else 'Unknown' end)
...