Clever Geek Handbook

The stored procedure for changing a bit flag cannot use Enums, since many applications will modify it, what should I do?

The database has a column of type INT (Sql Server).

This int value is used in the bit flag, so I'll be AND'ing and OR'ing on it.

I need to pass a parameter to my sproc and this parameter will represent a specific flag element.

I would normally use an enum and pass the int representation to the sproc , but since many different modules will access it, there will be no practical definition of my enum for them (if it is changed, it will be a headache to roll it out).

So I have to use "string" or magic-number as parameter value, then in my sproc I will do:

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


source to share


2 answers


You can use string and 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
+2


source


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 say there will be more than two flags and that more than one flag will have the same final meaning



0


source






More articles:


All Articles