Misleading SQL Error
Here is the error:
Msg 245, Level 16, State 1, Procedure ApprovalWorkBenchGet, Line 10
Syntax error converting the nvarchar value 'Day' to a column of data type int.
UPDATE: The cause was a join between two disparate typed columns. For some reason I got the impression that adding these parentheses made a difference.
WRONG ANSWER
Here is the cause:
and @ApproverID is null or @ApproverID = e.EmpApproverID
Here is the solution:
and (@ApproverID is null or @ApproverID = e.EmpApproverID)
When in doubt, use parentheses.
posted by Amboy Observer @ 3:40 PM
2 Comments:
I don't think the error message is addressing the problem line of code you listed. The NVarChar 'Day' doesn't look like an @ApproverID.
So the error message is addressing another code segment that you didn't list.
The OR operator will always be evaluated after the AND operator. Since you have both an AND and an OR, you need to put parenthesis around the two OR expressions in order to get the logic you want. The syntax error just happened by coincidence.
Are you certain you want to select a record if @ApproverID Is Null? That means you are selecting all records if you don't provide a parameter for @ApproverID.
Now I'm wondering what this will do to performance. Maybe it would be better to evaluate @ApproverID and then either apply the filter to e.EmpApproverID, or don't apply the filter at all:
IF @ApproverID IS NULL
--exclude the AND clause
ELSE
AND e.EmpApproverID = @ApproverID
Depending on how you're building the SQL expression, this code may or may not be useful.
bop-op, I don't think it's allowed to have an IF statement around a WHERE clause, or part of it.
Yes, the goal of this type of select is to "ignore" the parameter if its value is NULL.
Performance does suffer, but it is the only way I am aware of to have variable numbers of filter criteria and not have to generate dynamic sql.
Post a Comment
<< Home