Solved

Trouble understanding equation involving ampersand

Posted on 2016-11-04
5
69 Views
Last Modified: 2016-11-05
Hello all,

I have little experience with how the ampersand works in the context of SQL Server so I'm hoping someone can help me in that regard.  I have a stored procedure that returns a bit indicator for a status.  Within the sproc I have the following code:

declare @TeamEventEnabled int = 0x1000000;

iif(@EventStartDate > @NewTeamEventEffectiveDate AND @EventType = 0 AND @MyAccountStatus & @TeamEventEnabled > 0, 1, 0);

Open in new window


The other variables are parameters fed into the sproc.  The first two conditions of the IIF statement are straight forward but the last one involving @MyAccountStatus (this is an int) and @TeamEventEnabled puzzles me.  What exactly is it evaluating?

Thanks in advance,
Glen
0
Comment
Question by:jisoo411
  • 2
  • 2
5 Comments
 
LVL 18

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 250 total points
ID: 41875048
It is BitWise AND.

It performs a bitwise logical AND operation between two integer values.

The & bitwise operator performs a bitwise logical AND between the two expressions, taking each corresponding bit for both expressions.

SQL Server will internally convert the parameters into Bits ( binary format and then apply the operation ), Example Below

SELECT 7 & 2

0000 0000 0000 0111  -- 7
0000 0000 0000 0010  -- 2
-------------------  
0000 0000 0000 0010  -- Only the 2 bit is common in both 7 and 2

For each single digit if the value is 1 then we get 1 else 0

0000 0000 0000 0111
0000 0000 0000 0010

for 2nd position from right we have 1 & 1 , so if value are 1 & 1 then we get 1.

For other 1 & 0 , 0 & 0 , 0 & 1 --- we will get 0.

Below is how the binary equivalent is calculated.

For 7 -- its internal binary representation is 0000 0000 0000 0111.  Divide the number by 2 take reminder until your get 0.

7 /  2  ---- 1
3 / 2 ------- 1
1/ 2  ------- 1

All remaining will be zero since we have to make it 16 digits.  <<16 digits Binary System>>
So 0000 0000 0000 0111

Binary
Source -

http://www.wikihow.com/Convert-from-Decimal-to-Binary#/Image:Convert-from-Decimal-to-Binary-Step-3-Version-4.jpg

Hope it helps
!!
0
 
LVL 11

Accepted Solution

by:
Nakul Vachhrajani earned 250 total points
ID: 41875313
You would basically use bitwise AND in a SQL query to figure out what two values have in common.

Because an integer is actually a collection of bits (little 1s and 0s), This is typically used when you want to represent a combination of switches (1 = on, 0 = off) as a single integer. You can then use a bitwise AND to find out overlaps between two sets of switches.

Here's a small example that I could come up with:

/*
FYI - Pardon my ignorance of your enumeration values, I am just going ahead with
      some samples to demonstrate the concept

Assume that an Account Status represents a total of 4 different values
    - Bit #1: Account is Active
    - Bit #2: Customer has a Debit Card
    - Bit #3: Customer has a bank locker
    - Bit #4: Customer has open service request

Similiarly, a TeamEvent could be a combination of
     - Bit #1: Customer Requested Important notifications to be sent
     - Bit #2: Debit Card Dispatched
     - Bit #3: Bank Locker annual payment due
     - Bit #4: Appointment with representative available
*/
--Now, assume I have 5 customers:
DECLARE @MyAccountStatus1 INT =  8; --1000 - only active customer, no other services availed
DECLARE @MyAccountStatus2 INT = 12; --1100 - Active customer, with debit card
DECLARE @MyAccountStatus3 INT = 14; --1110 - Active customer, with debit card and bank locker
DECLARE @MyAccountStatus4 INT = 10; --1010 - Active customer, without debit card, but with bank locker
DECLARE @MyAccountStatus5 INT =  1; --0001 - New customer, would like to meet with a representative

--Now, assume that we need to send out notifications to all active customers holding a debit card
DECLARE @TeamEventEnabled INT = 12 --1100

--Let's see who will get the notification
SELECT @MyAccountStatus1 & @TeamEventEnabled AS Customer1, --Result = 8, does not have a debit card
       @MyAccountStatus2 & @TeamEventEnabled AS Customer2, --Result = 12, send notification
       @MyAccountStatus3 & @TeamEventEnabled AS Customer3, --Result = 12, send notification
       @MyAccountStatus4 & @TeamEventEnabled AS Customer4, --Result = 8, does not have a debit card
       @MyAccountStatus5 & @TeamEventEnabled AS Customer5; --Result = 0, do not send notification

--Now, let's schedule appointments
SET @TeamEventEnabled = 1;

--Let's see who will get the notification
SELECT @MyAccountStatus1 & @TeamEventEnabled AS Customer1, --Result = 0, do not send notification
       @MyAccountStatus2 & @TeamEventEnabled AS Customer2, --Result = 0, do not send notification
       @MyAccountStatus3 & @TeamEventEnabled AS Customer3, --Result = 0, do not send notification
       @MyAccountStatus4 & @TeamEventEnabled AS Customer4, --Result = 0, do not send notification
       @MyAccountStatus5 & @TeamEventEnabled AS Customer5; --Result > 0, confirm appointment

Open in new window

0
 

Author Comment

by:jisoo411
ID: 41875630
Thank you both for explaining it to me, I think I understand the basic functionality.  It's basically comparing two integers at a binary level.  I think the only follow up question would be why do this in the first place?  It looks like that condition is just comparing two integers, so why the need to examine it at such a detailed level?
0
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41875665
It all depends on the original design. Rather than storing 4 related bit fields in 4 different columns, designers may want to combine it into a single integer.

It's more of a design decision that the application designers would have taken to accommodate multiple related bit fields into a single field/value so that rather than exchanging multiple values, you only need to work with one value.
0
 

Author Closing Comment

by:jisoo411
ID: 41875667
Thank you both for your help with understanding this concept!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now