Converting data type varchar to numeric

Question

Msg 8114, Level 16, State 5, Procedure spGetDetails, Line 88
Error converting data type varchar to numeric.

I have already converted this @mfr_id to int type then also getting the above error.

I'm getting error while executing stored procedure.

The line which I'm getting error is:

if(@mfr_id = 5)

Answer 1

Updated answer

So it seems that @mfr_id is a varchar. To avoid the syntactic issue use the answer in OMG Ponies post.

But you also say that it is storing the string "1 2, 3, 4.....". So semantically are you wanting the IF statement to be true if it contains the value '5'?

If so you might need something like this

set @mfr_id = REPLACE(@mfr_id, ' ','')
if ((@mfr_id LIKE '5,%') OR (@mfr_id LIKE '%,5,%') OR (@mfr_id LIKE '%,5'))

Original Answer - Obsolete

if(CONVERT(int, @mfr_id) = 5)

should do the trick hopefully. See http://msdn.microsoft.com/en-us/library/ms187928.aspx for details. Although actually I think it should be implicitly converted. What is the value of @mfr_id? It should tell you this in the error message I think.

Answer 2

Use:

if(@mfr_id = '5')

Value comparisons have to be the same data type, or there has to be implicit data type conversion. Explicit conversion -- which is when you use CAST/CONVERT -- is ideal for maintenance because the operation is obvious.

Depending on your needs, the ISNUMERIC function might help. And be careful to define a length to your [n]varchar variables.

Answer 3

This answer is written using Oracle's PL/SQL syntax and one of the Oracle regular expression routines. I don't know T-SQL well enough to transcribe it but I expect that similar capabilities are available:

FOR aRow IN
  (WITH DATA AS (SELECT @mfg_id AS S FROM DUAL)
     SELECT REGEXP_SUBSTR(S, '[^ ,]+', 1, LEVEL) AS NUM_STRING
       FROM DATA
       CONNECT BY LEVEL < LENGTH(S) - LENGTH(REGEXP_REPLACE(S, '[ ,]', '')))
LOOP
  IF aRow.NUM_STRING = '5' THEN
    NULL;  -- do something appropriate here
  END IF;
END LOOP;

Share and enjoy.