0

I'm modifying a stored procedure and then executing it. Basically it's about inserting monthly claim table (which I have imported and all variables are VARCHAR(MAX)) into existing big table with previous historical data.

Primarily what the stored procedure is doing is changing the data types for all variables in accordance with existing big table variable data types. For the most part I have been able to run the stored procedure and update the big table. For one of the files, when I run the procedure, it gave me following error:

Msg 8114, Level 16, State 5, Procedure InsertClaimsData, Line 10 [Batch Start Line 8] Error converting data type varchar to float

I'm unable to figure out which exact variable is causing this issue as there are more than 10 variables where the original writer of this code had used the following type of conversion code:

CASE WHEN ISNUMERIC([INSURED ZIP]) = 1 THEN CAST([INSURED ZIP] AS FLOAT) ELSE 0 END
,CASE WHEN ISNUMERIC([BILLING PROVIDER TAX ID]) = 1 THEN CAST([BILLING PROVIDER TAX ID] AS FLOAT) ELSE 0 END

Above is just a small snippet of the code.

Is there a better way to write this code so that in case there is some bad data, it ignores it and does not give an error? Or any way to know which variable or records are responsible for this error and i can then fix that?

I'm using SSMS 17

e_i_pi
  • 4,590
  • 4
  • 27
  • 45
Dev
  • 1
  • 1
  • 1

1 Answers1

1

ISNUMERIC will return true for values that are kindof numeric, but not really, so it may not be completely ideal. There are many examples of where that throws up issues if you browse a bit. This answer provides some example of that.

You could use TRY_PARSE() or TRY_CAST() as an alternative and see if that helps rectify your issue.

Mr Moose
  • 5,946
  • 7
  • 34
  • 69
  • I agree. TRY_PARSE and TRY_CAST can be used to see if the value is "castable". If not you need to take some manual actions to fix the data. – Nick.Mc Jan 16 '19 at 00:07
  • Good to hear. Welcome to Stack Overflow too. I hope you get many other questions answered. – Mr Moose Jan 16 '19 at 03:09