Saturday, January 2, 2010

Strang Right() Function Behavior

I have a query that has been working for 6 months. All of a sudden it started giving me the error "Invalid length parameter passed to the RIGHT function". That query contains serveral select queries that are UNIONed together. A RIGHT() function is in one of those select queries. When I ran the select queries seperately, they all worked fine. But when I ran them together with the UNION, I got the error. This is very confusing.

The error went away after I put additional filtered in the query with Right() function. The additional filter is not necessary. But it got rid of the error.

My theory is that when SQL server parses the query, the Right() was verified against data that will be filtered out by the WHERE clause. I hope there is a way to verify that.

No comments: