conditional WHERE clause in TSQL, operators problem

In a SQL Serv开发者_高级运维er query I would like to have a conditional WHERE clause like this :

WHERE id_A = CASE WHEN @id != 0 THEN @id ELSE anything but @id END

I have no idea how to write the ELSE part. In one case I need to have WHERE id_A = @id but WHERE id_A != @id for the ELSE.

Thanks for your help.

Try something along the lines of this:

WHERE (@id != 0 AND id_A = @id) OR (@id = 0 AND id_A != @id)

Note that if the queries are likely to return significantly different row counts then you should instead consider separating this into two queries to make sure that you have an optimal query plan for both queries, something along the lines of:

IF @id != 0 THEN
BEGIN
    SELECT /* columns from table */
    WHERE id_A = @id
END
ELSE
BEGIN
    SELECT /* columns from table */
    WHERE id_A != 0
END

Unfortunately this means duplicating the rest of your SQL query, however it is likely to perform better in the case where one variant returns most of the table however the first variant only returns a single row.

Assuming id_a is not nullable

WHERE  id_a = CASE
                WHEN @id = 0 THEN CASE
                                    WHEN id_a <> 0 THEN id_a
                                  END
                ELSE @id
              END 

Should do it. Unless you use OPTION RECOMPILE and are on specific versions of SQL Server 2008 this will be inefficient however.

--will match always (if id_A can not be NULL
WHERE
    id_A = CASE WHEN @id <> 0 THEN @id ELSE id_A END

-- will be false always
WHERE
    id_A = CASE WHEN @id <> 0 THEN @id ELSE NULL END

These can be written as

 WHERE
    id_A = ISNULL(NULLIF(@id, 0), id_A)

 WHERE
    id_A = NULLIF(@id, 0)