dimanche 15 juillet 2018

IF Statement logic not working in TSQL --> What am I doing wrong

Been awhile since I been coding in TSQL, but I have an IF statement not working in a function. It must be a logic issue on my end but I can't see it.

If a piece of data, @pTownship is not blank and null than I am testing it to see if it is one, two, or three characters in length and returning the township number to the calling stored procedure. Following is my function code.

The issue is that when a piece of data, say 05N, is passed as @pTownship the outer IF is not true the else is being executed so my internal IF conditions are never being executed. Since @pTownship is 05N it's NOT '' or NULL so why isn't my true condition being executed?

Thanks in advance for a second set of eyes.

{ CREATE FUNCTION core.fnTownshipNumber ( @pTownship VARCHAR(50) ) RETURNS INT

AS
BEGIN

  DECLARE 
    @TownshipNumber INT,
    @InputLength INT;

  IF @pTownship <> '' AND @pTownship <> NULL
     BEGIN
       SET @InputLength = LEN(@pTownship);

       -- single character, based on the data, single character is always number so return it
       IF @InputLength = 1
          SET @TownshipNumber = CONVERT(INT, @pTownship);

       -- double character, based on the data, double char are always number so return it
       IF @InputLength = 2
          SET @TownshipNumber = CONVERT(INT, @pTownship);

       -- triple character, based on the data, third char is always direction so return first two   
       IF @InputLength = 3
          SET @TownshipNumber = CONVERT(INT, SUBSTRING(@pTownship, 1, 2));
     END;
  ELSE
     BEGIN
       SET @TownshipNumber = NULL;
     END;

  RETURN @TownshipNumber;
END

}

Aucun commentaire:

Enregistrer un commentaire