CODEWITHSUNDEEP
sql-server
Thomas
Thomas

Reputation: 80

SQL Server: Error converting data type varchar to numeric (Strange Behaviour)

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?

Upvotes: 0

Views: 105

Answers (1)

Slava Murygin
Slava Murygin

Reputation: 1955

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

Upvotes: 1

Related Questions