Flow Control Problem with multiple statements

  • I am creating a function that should retrieve a numerical value from notes. This can be represented a few different ways and I used a number of IF Statements to accomplish this. For some reason, it seems to be executing code in the Final IF Statement. The simplified code at the top works and produces '36' as expected.  In my actual script bottom, it seems to be executing the final IF statement which it should not.  If I run the first Select statement in my code in SSMS, I get '36' returned which is expected. It does not seem to enter the second IF Statement which is expected but it does the third as @result comes back as CB which is what that code returns. Also, If I comment out the if statements with isnumeric, I do get 36 for this entry but it messes up other entries where the value is stored in a different place.  Since @result is a value where isnumeric(@result) should be 1, I don't see why it is going into that IF Statement. The object here is to keep going until I get a string that can be represented as a number and then stop looking. Any help would be appreciated

    Declare @clientid int
    Declare @clienttype Nvarchar(10)
    Declare @clientcreatedate datetime
    Declare @leadcreatedate datetime
    Declare @result Nvarchar(5)
    Set @clientid = 1
    Set @clienttype = 'Client'
    Set @clientcreatedate = '2020-03-31 00:00:00'
    Set @leadcreatedate = '2012-08-09 17:49:00'

    If @ClientType = 'Client' and @clientcreatedate < '2022-08-01 00:00:00' and @leadcreatedate < '2022-08-01 00:00:00'
    Begin
    Select @result = '36 '
    If @result is null or isnumeric(@result) <> 1
    Begin
    Select @result = 'test'
    End

    If @result is null or (@result = '') or (isnumeric(@result) <> 1)
    Begin
    Select @Result = 'Test1'
    End
    End
    Print @result

    If @ClientType = 'Client' and @clientcreatedate < '2022-08-01 00:00:00' and @leadcreatedate < '2022-08-01 00:00:00'
    Begin
    Select @result = (select top 1 ltrim(rtrim(SUBSTRING(Task6,PatINDEX('%Length of DMP (# of mos):%',Task6) +25,4)))
    from [dbo].[Custom1] where CustomID in (Select CustomID from [dbo].[Header] where clientid = @ClientID)

    If @result is null or result = '' or isnumeric(@result) <> 1
    Begin
    Select
    @result = (Select top 1 ltrim(rtrim(Substring(notes,1,3)))
    from
    [dbo].[CustomNotes] where CustomID in( Select CustomID from [dbo].[History] where clientid =
    @ClientID)
    End
    If @result is null or (@result = '') or (isnumeric(@result) <> 1)
    Begin
    Select
    @Result = (select top 1 ltrim(rtrim(SUBSTRING(tasks,1,3)))
    from [dbo].[tasks] where CustomIDID in (Select CustomID from [dbo].[History] where clientid = @ClientID)
    End
    END

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Doesn't it just mean that the second IF failed to return a non-null, non-blank, numeric and so qualified for the third IF?

    Put a SELECT @Result after the end and before the next of each of the IF statements and see.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thanks for your reply Jeff. I put the Select statement in and I could see the @result value changing as it was following IF paths it shouldn't because the expected IsNumeric condition wasn't being evaluated. I copied the value into notepad++ and could see there was a trailing space. This was anticipated as I was grabbing this from a note but Isnumeric should have been able to make a number out of it. Strangely, I even tried trim() with it and I still couldn't get isnumeric to evaluate it. What did work was using Substring(@result,1,2) as I knew the length for this specific result. Since the length was going to vary between 1-4 characters, I created a custom function to strip everything but numeric characters and applied the Inumeric function to that (ie  Isnumeric(dbo_stripnonnumerics(@result)).  Longwinded way top doing it but it seems to work. Still not sure why @result with the trailing space failed or the trim function didn't work.

  • Perhaps it wasn't a trailing space.  Perhaps it's a "hard space" (Char 160 in ASCII) or something else like a "Control Character" (ASCII characters <= CHAR(31), which includes things like tabs, Cr/Lf, and a bunch of other stuff.

    ISNUMERIC isn't the best thing to use.  For example, try the following and see that the values actually can be interpretted as a numeric value....

     SELECT ISNUMERIC('2d3'),ISNUMERIC('2e3'),ISNUMERIC(','),ISNUMERIC(',,,,,,'),ISNUMERIC('$'),ISNUMERIC('.')

    ... and there's a shedload more.  Here's a bit of reading on it where it is explained why it must not be used as an IsAllDigits function...

    https://www.sqlservercentral.com/articles/why-doesn%e2%80%99t-isnumeric-work-correctly-sql-spackle

    ...and there's more that follows that in the discussion that you can get to by clicking on the "Join the discussion and add your comment" link at the bottom of the article.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply