Simple Function with CASE and Null error !

• Digs

  SSCertifiable

  Points: 7333

  More actions

  May 6, 2005 at 1:14 pm

  #164426

  I just want to have a simple function that can changes things, and detect a NULL. I know I can leave the NULLS  to the ELSE part of function, but dont want that, I want nulls to return a allocate result.

  What am I doing wrong ?

  Error Message: Incorrect syntax near the keyword 'IS'

  CREATE FUNCTION dbo.SimpleCaseExample (@Input VARCHAR(5))

  RETURNS  VARCHAR(5)

  AS

  BEGIN

   DECLARE @Output AS VARCHAR(5)

   SELECT @Output = CASE @Input

    WHEN @Input IS NULL THEN 'None' --ERROR HERE

    WHEN '1' THEN '2'

    WHEN '2' THEN '3'

    WHEN '4' THEN '5'

    WHEN '6' THEN '7'

    ELSE '0'

    END

  RETURN @Output

  END

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  May 6, 2005 at 1:21 pm

  #557454

  Declare @Input as varchar(25)

  set @Input = null

  Select case when @Input is null then 'none' else @Input end, isnull(@Input, 'none')

  set @Input = '8'

  Select case when @Input is null then 'none' else @Input end, isnull(@Input, 'none')

  or from your function :

  SELECT @Output = CASE

  WHEN @Input IS NULL THEN 'None'

  WHEN @Input = '1' THEN '2'

  WHEN @Input = '2' THEN '3'

  WHEN @Input = '4' THEN '5'

  WHEN @Input = '6' THEN '7'

  ELSE '0'

  END