Error Converting Varchar to Numeric

1

hi i tried running the code below its part of a larger stored procedure to run a payroll. but i get an error message at this point saying Msg 50000, Level 11, State 1, Procedure spPayrollRun, Line 292
Error converting data type varchar to numeric.

Can some help please :slight_smile:

MY CODE
SELECT d.Id, SUM(ROUND(CASE WHEN T1.DeductionBase > dtd.AmountTo THEN (dtd.AmountTo - dtd.AmountFrom) WHEN T1.DeductionBase > dtd.AmountFrom

THEN T1.DeductionBase - dtd.AmountFrom ELSE 0 END * (dtd.Rate / 100 * dtd.EmployeeShare / 100),2)) AS EmployeeAmount,

SUM(ROUND(CASE WHEN T1.DeductionBase > dtd.AmountTo THEN (dtd.AmountTo - dtd.AmountFrom) WHEN T1.DeductionBase > dtd.AmountFrom

THEN  ISNULL(T1.DeductionBase - NULLIF(dtd.AmountFrom,''),0) END * ISNULL(dtd.Rate / 100 * NULLIF(dtd.EmployerShare / 100,''),0),2)) AS EmployerAmount,

AVG(T1.DeductionBase) AS DeductionBase

FROM T1 JOIN (Deduction d JOIN (DeductionType dt JOIN DeductionTypeDet dtd ON dt.Id = dtd.DeductionTypeId) ON d.DeductionTypeId = dt.Id) ON
T1.StaffId = d.StaffId AND T1.PaymentGroupId = dt.PayGroupId WHERE d.IsActive ='True' AND (d.IsPermanent ='True' OR d.DurationBalance > 0) AND dt.DeductionCategoryId = 'STAT' AND NOT dt.PayGroupId IS NULL AND
EXISTS(SELECT * FROM Staff s WHERE s.Id = d.StaffId AND (s.EndDate IS NULL OR s.EndDate BETWEEN @StartDate AND @EndDate)) AND
EXISTS(SELECT * FROM PayPeriod pp WHERE d.StartDate <= pp.EndDate AND pp.Id =@PayPeriodId) AND NOT EXISTS(SELECT * FROM RepaymentHoliday rh WHERE rh.DeductionTypeId =d.DeductionTypeId AND rh.StaffId =d.StaffId AND rh.PayPeriodId =@PayPeriodId)

GROUP BY d.Id](http://mycode)

2

The error message says that somewhere in your code you are trying to convert a character string to a number and that is failing. You may not be doing it explicitly. It might be an implicit conversion. I see places in your code where implicit conversions happen. For example, this:

T1.DeductionBase - NULLIF(dtd.AmountFrom, '')

if dtd.AmountFrom is of varchar type and if it contains strings that cannot be converted to numeric data, you will see the failure message you saw. See this demo:

declare @x1 int, @x2 varchar(32) = 'abc';
set @x1 = 32;

select @x1 - nullif(@x2, '');

This type of problem happens because of using wrong data types. If something is supposed to be numeric, store that in a column that has numeric data type.

3

In the first instance I would use IsNumeric() to find any values (of columns with non-numeric datatypes) which fail that numeric test. IsNumrric() is not perfect because it has false positives, but chances are good that it will find the goofy data for you.

Just change your SELECT clause to:

SELECT d.Id, T1.DeductionBase, dtd.AmountTo, dtd.AmountFrom, dtd.Rate, dtd.EmployeeShare

and add to the WHERE clause something like:

	AND
	(
		IsNumeric(T1.DeductionBase) = 0
		OR IsNumeric(dtd.AmountTo) = 0
		OR IsNumeric(dtd.AmountFrom) = 0
		OR IsNumeric(dtd.Rate) = 0
		OR IsNumeric(dtd.EmployeeShare) = 0
	)

(only need to include the non-numeric datatype columns)