Error checking in T-SQL Script

Question

I am self taught in T-SQL, so I am sure that I can gain efficiency in my code writing, so any pointers are welcomed, even if unrelated to this specific problem.

I am having a problem during a nightly routine I wrote. The database program that is creating the initial data is out of my control and is loosely written, so I have bad data that can blow up my script from time to time. I am looking for assistance in adding error checking into my script so I lose one record instead of the whole thing blowing up.

The code looks like this:

    SELECT convert(bigint,(SUBSTRING(pin, 1, 2)+ SUBSTRING(pin, 3, 4)+ SUBSTRING(pin, 7, 5) + SUBSTRING(pin, 13, 3))) AS PARCEL, taxyear, subdivisn, township, propclass, paddress1, paddress2, pcity

    INTO [ASSESS].[dbo].[vpams_temp]

    FROM [ASSESS].[dbo].[Property]

    WHERE parcelstat='F' 

    GO

The problem is in the first part of this where the concatenation occurs. I am attempting to convert this string (11-1111-11111.000) into this number (11111111111000). If they put their data in correctly, there is punctuation in exactly the correct spots and numbers in the right spots. If they make a mistake, then I end up with punctuation in the wrong spots and it creates a string that cannot be converted into a number.

My suggestion is to put the strings into a temp table. Then get rid of the hyphens and periods. Finally, use only those strings that are integers. — Dan Bracuk
– Dan Bracuk, Commented Feb 24, 2017 at 15:02

Mihai Ovidiu Drăgoi · Accepted Answer · 2017-02-24 15:00:34Z

2

How about simply replacing "-" and "." with "" before CONVERT to BIGINT?

To do that you would simply replace part of your code with

SELECT CONVERT(BIGINT,REPLACE(REPLACE(pin,"-",""), ".","")) AS PARCEL, ...

Hope it helps.

edited Feb 24, 2017 at 15:00

answered Feb 24, 2017 at 14:57

Mihai Ovidiu Drăgoi

1,3171 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Matt Syvertson Over a year ago

That sounds reasonable, how would that code look? If that solves this issue, I would love to implement that. Additionally, I have other concatenation problems that this would not fix and would still like to know if there is a method to check data to see if it conforms. In C# and other languages, you could read line by line and "TRY" to insert the line, and reject the one line if it doesn't conform.

Mihai Ovidiu Drăgoi Over a year ago

The "DBMS way" to do things is to check if there are any rows that don't conform to rules before the INSERT - if none, do the insert, otherwise return the "bad" rows to whoever can correct them. If need be, you can still insert the "good" ones.

Matt Syvertson Over a year ago

I have created a routine that takes data from 4 sources and combines and displays the records in a web environment for public consumption. The data that is being converted here adds value to the product, but is not critical. My preference would be for all the good records to be added and some note come back to me, so I can tell the yahoos in the other office that there data is bad. You said "The "DBMS way" to do things is to check if there are any rows that don't conform to rules before the INSERT." Can I do that to create a report of bad data, and then just insert the good ones?

Mihai Ovidiu Drăgoi Over a year ago

Of course you can, but I suggest a different question on SO for that. Good luck!

Gordon Linoff · Accepted Answer · 2017-02-24 14:59:27Z

1

First, I would use replace() (twice). Second, I would use try_convert():

SELECT try_convert(bigint,
                   replace(replace(pin, '-', ''), '.', '')
                  ) as PARCEL,
       taxyear, subdivisn, township, propclass, paddress1, paddress2, pcity
INTO [ASSESS].[dbo].[vpams_temp]
FROM [ASSESS].[dbo].[Property]
WHERE parcelstat = 'F' ;

You might want to check if there are other characters in the value:

select pin
from [ASSESS].[dbo].[Property]
where pin like '%[^-0-9.]%';

answered Feb 24, 2017 at 14:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Matt Syvertson Over a year ago

I definately like the "try_convert" part. That is what I was looking for. I THINK. What is the behavior if it comes across non-conforming data using the try_convert?

S3S Over a year ago

@MattSyvertson try-convert just returns NULL if the convert for that record fails, and continues the execution. So, it lets you convert all values that are able to be converted without causing a fatal error in you execution.

S3S · Accepted Answer · 2017-02-24 14:59:08Z

0

Why not just:

select cast(replace(replace('11-1111-11111.000','-',''),'.','') as bigint)

answered Feb 24, 2017 at 14:59

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Comments

ahmed abdelqader · Accepted Answer · 2017-02-24 15:03:29Z

0

simply, use the next code:-

declare @var varchar(100)
set @var = '11-1111-11111.000'
select convert(bigint, replace(replace(@var,'-',''),'.',''))

Result:-

11111111111000

answered Feb 24, 2017 at 15:03

ahmed abdelqader

3,58220 silver badges38 bronze badges

Collectives™ on Stack Overflow

Error checking in T-SQL Script

4 Answers 4

4 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related