sql stored procedures and bitwise operators

I have a categories table:

    id;description;special
    ----------------------
    1;Spares;TRUE
    2;Accessories;TRUE
    4;Consumables;TRUE
    8;Services;TRUE
    11;Printer;FALSE
    12;Monitor;FALSE
    13;Other;FALSE

The special field designates special categories that have fixed ids and cannot be deleted nor modified. 开发者_开发知识库I've assigned bitwise OR-able ids to those.

Then I have items, each item belongs to a category (with a 1:n relationship).

Now I'd like to write a stored procedure that takes an input parameter containing an OR-ed combination of ids:

1 I want spare parts
2 I want accessories
4 I want consumables 
**5 I want consumables AND spare parts**

etc

If the parameter is NULL, then I want every item regardless of its category.

This is quite easy, say the parameter is called _or_category, then the WHERE clause could be something like:

SELECT
    *
FROM
    items I 
JOIN 
    categories C ON (C.id = I.category)
WHERE
    (_or_category IS NULL) OR (C.special = TRUE AND C.id | _or_categoria = _or_categoria)
;

First problem: *edit: sorry this is not a problem, since I have C.special=TRUE in the WHERE clause.* category 12 could be "seen" as id=8 OR 4, thus if I want to select only the consumables, I would get also the monitors!

Second problem: I don't know how to specify when I want all the items which are NOT a service (cat: 8).

Second problem: I don't know how to specify when I want all the items which are NOT a service (cat: 8)

If I understand your question I think you're looking for the bitwise Invert bits ~

for example

C.special = TRUE AND  (~C.ID | or_categoria = _or_categoria)

You need

0x01 - Spares
0x02 - Accessories
0x04 - Consumables
0x08 - Services
0x10 - Printer
0x20 - Monitor
0x40 - Other

And all things not services = 0x7F & ~0x08

Edit: If you only want the first 4 things to be flags it is not much different. The first 4 bits are reserved exclusively for your bit comparisons. So you cannot have any additional ids that would require a value in the first 4 bits (from the right)...

0x01 - Spares
0x02 - Accessories
0x04 - Consumables
0x08 - Services
0x10 - Printer
0x20 - Monitor
0x30 - Other

And again, Ax(~Sx) = 0x3F & ~0x08