Advanced Search

Using a BitMask in a Where Clause

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. BEGIN 
  2. DECLARE @MailDelivery INT, 
  3.         @EmailDelivery INT, 
  4.         @FaxDelivery INT, 
  5.         @ParamVal INT, 
  6.         @ZeroVal INT
  7.  
  8. SET @MailDelivery  = 1 --(0001)
  9. SET @EmailDelivery = 2 --(0010)
  10. SET @FaxDelivery   = 4 --(0100)
  11.  
  12. SET @ParamVal = 1
  13. SET @ZeroVal =  0
  14.  
  15. IF @ParamVal = 0
  16. SET @ZeroVal = NULL
  17.  
  18. SET @ParamVal = isnull(@ParamVal, 0)
  19.  
  20. SELECT Id, 
  21.        --BusinessEventDate, 
  22.        DeliveryFlags, 
  23.        (isnull(DeliveryFlags,0) & @ParamVal) AS BitMask 
  24. FROM   Mailsets
  25. WHERE (BusinessEventDate > '8/28/2007')
  26. --Check the appropriate bit.  
  27.   AND (
  28.         (isnull(DeliveryFlags,0) & @ParamVal) = @ParamVal + isnull(@ZeroVal, isnull(DeliveryFlags,0))
  29.       )
  30.  
  31. END 
  32. GO

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.