0

I'm working on a legacy system using SQL Server in 2000 compatibility mode. There's a stored procedure that selects from a query into a virtual table.

When I run the query, I get the following error:

Error converting data type varchar to numeric

which initially tells me that something stringy is trying to make its way into a numeric column.

To debug, I created the virtual table as a physical table and started eliminating each column.

The culprit column is called accnum (which stores a bank account number, which has a source data type of varchar(21)), which I'm trying to insert into a numeric(16,0) column, which obviously could cause issues.

So I made the accnum column varchar(21) as well in the physical table I created and it imports 100%. I also added an additional column called accnum2 and made it numeric(16,0).

After the data is imported, I proceeded to update accnum2 to the value of accnum. Lo and behold, it updates without an error, yet it wouldn't work with an insert into...select query.

I have to work with the data types provided. Any ideas how I can get around this?

Improve this question
1
  • 1
    Can you show us the query and table structure? Commented Dec 19, 2018 at 22:52

1 Answer 1

Reset to default
1

Can you try to use conversion in your insert statement like this:

SELECT [accnum] = CASE ISNUMERIC(accnum) WHEN 0 THEN NULL ELSE CAST(accnum AS NUMERIC(16, 0)) END
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

That works. The weird thing is that the code I had before has remained unchanged for at least 4 years and this only occurred now. I extracted all the new bank account numbers and they look fine (did regex checks etc to check for spaces or non-numeric characters etc.) It's still a mystery why this occurred all of a sudden...
Be careful with that code. You might have come up with NULLs where a person might see a number. Something like "123456789-1" will produce NULL.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.