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
August 20, 2022 at 11:10 pm
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
Change is inevitable... Change for the better is not.
August 21, 2022 at 5:53 am
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.
August 21, 2022 at 8:08 pm
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
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply