Logical XOR operator in T-SQL (SQL Server 2000)

Hi all....

I was wondering why SQL Server 2000 lacks a logical XOR operator. Do you have a ready to use function that implements it?

How can I program one by myself? I know that A XOR B is equivalent to NOT A * B + A * NOT B. So I tried to create this function :

CREATEFUNCTION XOR

(

-- Add the parameters for the function here

@.A BIT,

@.B BIT

)

RETURNS BIT

AS

BEGIN

RETURNNOT @.A AND @.B OR @.A +NOT @.B

END

But it didn't compile.

How can I pass boolean values as parameters? any other hint?

Thanks

Jaime

There is an XOR operator -- it is the caret (^). In the Books On-line look up operators(symbols) bitwise.|||

As you said, that is a bitwise operator, not a logical one.

For example, using the ^ I couldn't do something like :

SELECT *

FROM TABLE

WHERE (A = 10) ^ (B = 5)

which should return records when either A = 10 or B = 5, but if conditions are both satisfied or both not satisfied, I don't want records to be returned.

Another suggestion to accomplish that? Of course this is a simplified scenario. In case of 2 conditions I can do perfectly :

WHERE (A <> 10 AND B = 5) OR (A = 10 AND B <> 5)

The only solution I see is to implement a XOR function myself and to nest them, but I couldn't (or don't know how) pass a boolean value as a function parameter. If you know how, please tell me.

Thanks

Jaime

|||

There is no boolean data type in TSQL and there are only few system built-ins like CONTAINS that can be used directly in a WHERE clause. So you need to use the XOR operator and test for the return value of expression like:

WHERE (case A when 10 then 1 else 0 end)^(case B when 5 then 1 else 0 end) = 1

|||? The reason your UDF does not work is that BIT is not the same as BOOLEAN. There is no Boolean type in SQL Server, so you can't do: SELECT * FROM Table WHERE Column Instead, (assuming that Column is typed as BIT) you have to do: SELECT * FROM Table WHERE Column = 1 Anyway, it's quite easy to do XOR inline: SELECT * FROM Table WHERE (Column1 = 1 AND Column2 <> 1) OR (Column1 <> 1 AND Column2 = 1) -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Jaime Stuardo@.discussions.microsoft.com> wrote in message news:b2649891-1305-4b27-a074-5b0a896807e8@.discussions.microsoft.com... Hi all.... I was wondering why SQL Server 2000 lacks a logical XOR operator. Do you have a ready to use function that implements it? How can I program one by myself? I know that A XOR B is equivalent to NOT A * B + A * NOT B. So I tried to create this function : CREATE FUNCTION XOR ( -- Add the parameters for the function here @.A BIT, @.B BIT ) RETURNS BIT AS BEGIN RETURN NOT @.A AND @.B OR @.A + NOT @.B END But it didn't compile. How can I pass boolean values as parameters? any other hint? Thanks Jaime