Stored procedure to modify bit flag, can't use Enums since many apps will be modifying it, what should I do?

Question

Hi,

There is a column in a database that is of type INT (Sql server).

This int value is used at a bit flag, so I will be AND'ing and OR'ing on it.

I have to pass a parameter into my sproc, and that parameter will represent a specific flag item.

I would normally use an enumeration and pass the int representation to the sproc, but since many different modules will be accessing it it won't be practicial for them all to have my enum definition (if it is changed, it will be a headache to roll it out).

So should I use a 'string' or a magic-number as the parameter value, then in my sproc I will do:

IF(@blah = 'approved')
BEGIN
      // bit banging here
END

Answer 1

Why not use the old 0 and 1 for the flag? It is widely accepted as a bit switch already and there would be no confusion or misspelling as to what 0 and 1 mean. Unless you are saying that there will be more than 2 flags and that more than 1 flag will have the same ultimate meaning

Answer 2

You could use a string, and a CASE construct:

CREATE PROCEDURE BitBang(@Flag AS VARCHAR(50), @Id AS INT)
AS
BEGIN
  DECLARE @Bit INT

  SET @BIT = CASE @Flag
    WHEN 'approved'   THEN 16
    WHEN 'noapproved' THEN 16
    WHEN 'fooflag'    THEN 8
    WHEN 'nofooflag'  THEN 8
  END

  IF @Bit IS NOT NULL
  BEGIN
    IF LEFT(@Flag, 2) = 'no' 
    BEGIN
      UPDATE TheTable SET BitField = BitField & ~@Bit WHERE Id = @Id
    END
    ELSE
    BEGIN
      UPDATE TheTable SET BitField = BitField | @Bit WHERE Id = @Id
    END
  END
END