I have a simple stored procedure joining three tables. It has a couple DECLARE statements and a typical amount of WHERE conditions.
It works fine for the most part but during testing of certain fields in the WHERE clause, I'll get a
Server: Msg 8114, Level 16, State 5, Line 10
Error converting data type varchar to numeric.
Line 10 points to this line
Set @p = '%'
So I comment out all references to this parameter from the sproc and still get the error at line 10.
But if I edit the line..
OR UPR3.PAYROLCD IN ('BOD')
to
AND UPR3.PAYROLCD IN ('BOD')
...
the Error message disappears.
The final sroc will not even have this line, it's in strictly for testing the end results in Crystal Reports.
What would cause the above error to occur even after commenting out the supposed suspect line? Obviously, there is nothing wrong with Line 10 if the same error occurs whether it's commented out or not.
The only difference is changing the above line from OR to AND in the WHERE clause.
I'm looking for feedback on how my WHERE clause is created and if it's written in the best manner. Multiple JOINS can return unexpected results if not written correctly and am asking if another approach is more desirable.
Aside from the above error, which will disappear in the final version by default, the sproc works fine. I'm using SQL Server 2000
Code:
DECLARE @ChekMonth nchar(6), @ChekYear nchar(4)
DECLARE @p char(1)
Set @p = '%'
set @ChekMonth = 'NOV'
Set @ChekYear = '2007'
SELECT
...
FROM
UPR0Table UPR0
LEFT JOIN
EU01Table EU01
ON (UPR0.EMPLOYID = EU01.EMPID_I)
left JOIN
UPR3Table UPR3
ON (UPR0.EMPLOYID = UPR3.EMPLOYID)
WHERE
((EU01.MSTRING_I_1)>'00000001')
AND
(UPR3.UPRTRXAM > 0)
and
((UPR3.PYRLRTYP) between '1' and '2')
AND
UPR3.PAYROLCD NOT in('CONTR$','SEP2V')
AND
(UPR3.CHEKDATE LIKE @p + Left(@ChekMonth,3) + @p AND UPR3.CHEKDATE LIKE @p + Left(@ChekYear,4) + @p)
AND
UPR3.CHEKDATE > EU01.MDATE4_I
OR
UPR3.PAYROLCD IN ('BOD')
ORDER BY
EU01.MSTRING_I_1It works fine for the most part but during testing of certain fields in the WHERE clause, I'll get a
Server: Msg 8114, Level 16, State 5, Line 10
Error converting data type varchar to numeric.
Line 10 points to this line
Set @p = '%'
So I comment out all references to this parameter from the sproc and still get the error at line 10.
But if I edit the line..
OR UPR3.PAYROLCD IN ('BOD')
to
AND UPR3.PAYROLCD IN ('BOD')
...
the Error message disappears.
The final sroc will not even have this line, it's in strictly for testing the end results in Crystal Reports.
What would cause the above error to occur even after commenting out the supposed suspect line? Obviously, there is nothing wrong with Line 10 if the same error occurs whether it's commented out or not.
The only difference is changing the above line from OR to AND in the WHERE clause.
I'm looking for feedback on how my WHERE clause is created and if it's written in the best manner. Multiple JOINS can return unexpected results if not written correctly and am asking if another approach is more desirable.
Aside from the above error, which will disappear in the final version by default, the sproc works fine. I'm using SQL Server 2000