Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is this SQL IF doing with the Ampersand?

Tags:

sql

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.

like image 684
SCady Avatar asked Dec 18 '22 07:12

SCady


1 Answers

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.

like image 95
Bob Avatar answered Dec 19 '22 20:12

Bob