Hmmm... so the "id" column allows NULL. Are there any NULL values in this column? What is your ANSI_NULLS setting? I always work with ANSI_NULLS ON, so I'm not too sure...

Just being curious... if you rewrite the original query to use JOIN syntax and put the first ISNUMERIC into JOIN instead of WHERE, do you still get the error? Like...

One way to do this is to construct the where clause so that it always checks  whether the parameter is NULL or not, and acts accordingly.

WHERE

(@p1 IS NULL OR...

Unfortunately, ISNUMERIC is not reliable check because of values '.', '+' and '-' which return 1, but can not be converted into numbers.

However, this is not your problem, as it...

Hmm... there are still other settings that could influence it, like QUOTED IDENTIFIER, DATEFORMAT etc., but I'm afraid I have no ideas until I know DDL for both tables, and...

David,

you still didn't answer whether you changed the = NULL to IS NULL. If not, and your procedure is using different settings of ANSI NULLS than QA, then this is...

Probably the first row is correct and one of the other rows is causing problems - either because of duplicity, or because of data type or some constraint violation.

Extract the...

John,

I agree that it is a bad habit to use procedural code, unless it is necessary for some reason. I don't see any such reason in this example, however, I...

No, it doesn't change anything if you switch the conditions in WHERE clause. About the only thing where order of conditions can play any role is in CASE statements. Conditions of a...

Michael,

take care with NULL values... LEN(Known_as) is NULL (i.e., not 0), if Known_as is NULL. To make sure the CASE works as you want it, first transform NULL values to...

It uses the least common demonitator for syntax.

I knew there have to be some demons in the SQL!

Remi gave you almost the right answer (replace <> with = in the LEFT JOIN), just forgot to mention the second part of it - that is, if you want...

Paula,

most probably your programmers did not enforce referential integrity through the database itself. It is quite common that programmers ensure referential integrity on the application (user interface) level, but in such case if you write...

The main difference between cursor and temp table is, that cursor is processed row by row... while SQL with temp table is processed at once, as a set. Set-oriented operations...

Fine, now I have tested it and it works as I wanted it to (additionally, I noticed that payroll should not be considered when looking for duplicity, so this is...