CODEWITHSUNDEEP
sql
SCady
SCady

Reputation: 445

What is this SQL IF doing with the Ampersand?

What is this SQL IF doing with the Ampersand?

IF ((@TablesToDeleteFrom & 1) <> 0 AND
            (@TablesToDeleteFrom & 2) <> 0 AND
            (@TablesToDeleteFrom & 4) <> 0 AND
            (@TablesToDeleteFrom & 8) <> 0 AND
            (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
        BEGIN
    ...

This is from the aspnet mebership DB, inside the aspnet_Users_DeleteUser SP. The @TablesToDeleteFrom is declared as an int and defaulted to 0. I have not seen the use of the & like this before.

Upvotes: 4

Views: 3850

Answers (2)

Bob
Bob

Reputation: 99804

This is a bitwise operator. Basically Tables to delete from will store multiple tables in a single integer based field by creating a bitwise combination of values for tables. Here is an example.

1 - TableA
2 - TableB
4 - TableC
8 - TableD

TableA & TableB = 1 | 2 = 3. (You use the OR operator to get the result, and the AND operator to check for a result). So the value of 3 gets stored in the field. You can then use the ampersand operator to see if the value was set. 1 & 3 == 1, so TableA would be deleted. 4 & 3 = 0 so TableC would not be deleted.

Upvotes: 7

Alex Martelli
Alex Martelli

Reputation: 882181

As @Bob says, & is bitwise and; and so, by the way, a more compact equivalent of

IF ((@TablesToDeleteFrom & 1) <> 0 AND
            (@TablesToDeleteFrom & 2) <> 0 AND
            (@TablesToDeleteFrom & 4) <> 0 AND
            (@TablesToDeleteFrom & 8) <> 0 AND

is

IF ((@TablesToDeleteFrom & 15) = 15) AND

Upvotes: 5

Related Questions