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

Question

Been a while since I been coding in T-SQL, 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

Cee McSharpface · Accepted Answer · 2018-07-15T21:01:19.313

2

The <> operator is not meant to compare to NULL. When you need to check for NULL, use

@pTownship IS NOT NULL

or if you prefer,

NOT (@pTownship IS NULL)

Related: Not equal <> != operator on NULL

In your case, you are comparing against both the empty string and null, a more concise way to capture both cases would be this:

IF NULLIF(@pTownship, '') IS NOT NULL BEGIN
    /* ... */
END

You could omit the outer IF entirely, if you used this alternative:

SET @InputLength = LEN(COALESCE(@pTownship, ''));

The COALESCE function returns its second argument (the empty string in the example) when the first argument is NULL.

edited Jul 15 '18 at 21:01

answered Jul 15 '18 at 20:51

Cee McSharpface

8,493
3
36
77

Thanks! I appreciate the answer and the "better" / more concise explanation as well. – 5lb Bass Jul 15 '18 at 21:02
fyi: From the documentation for [`Len()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql): "Returns the number of characters of the specified string expression, **excluding trailing blanks**." – HABO Jul 16 '18 at 00:26

score 1 · Answer 2 · answered Jul 15 '18 at 21:09

I think you might need to use the following form:

IF (@pTownship IS NOT NULL) AND (LEN(@pTownship) > 0)

I am not as certain about the second term; variations might also work. Parentheses could perhaps be omitted, but I might prefer to keep them.

On this, see How do check if a parameter is empty or null in Sql Server stored procedure in IF statement?

score 0 · Answer 3 · answered Jul 16 '18 at 03:24

The following shows another way of implementing the logic with a case expression (not statement).

The first case implements the logic in a clear manner, though the initial when clause isn't really needed due to the else as a catch-all clause. The second variation takes advantage of Left() (or Substring()) tolerating a length greater than the input: it simply returns the entire string without error.

-- Sample data.
declare @Townships as Table ( Township VarChar(16) );
insert into @Townships ( Township ) values
  ( NULL ), ( '1' ), ( '23' ), ( '45N' ), ( 'Upper Lemming' );

-- Demonstrate conversion.
select Township,
  case
    when Township is NULL or Township = '' then NULL -- This can be left to the   else   clause.
    when Len( Township ) in ( 1, 2 ) then Cast( Township as Int )
    when Len( Township ) = 3 then Cast( Left( Township, 2 ) as Int )
    else NULL end as TownshipNumber,
  case
    -- Since   Left()   doesn't mind being asked for more characters than are available ...
    when Len( Township ) between 1 and 3 then Cast( Left( Township, 2 ) as Int )
    else NULL end as TownshipNumberX
  from @Townships;

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

3 Answers3