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