Geek Answers Handbook

What is SQL IF with Ampersand?

What does this SQL IF do with 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 database, inside the aspnet_Users_DeleteUser package. @TablesToDeleteFrom is declared as int and defaults to 0. I had not seen use before and so on.

+4
source share
2 answers

This is a bitwise operator. Basically, tables to be deleted from will store several tables in a single integer field, creating a bitwise combination of values ​​for the tables. Here is an example.

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

TableA and TableB = 1 | 2 = 3. (You use the OR operator to get the result, and the AND operator to check the result). Thus, the value 3 is stored in the field. You can then use the ampersand operator to find out if a value has been set. 1 and 3 == 1, so table A will be deleted. 4 and 3 = 0, so TableC will not be deleted.

+7
source

As @Bob says, & bitwise; and therefore a more compact equivalent

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

is an

 IF ((@TablesToDeleteFrom & 15) = 15) AND
+5
source

More articles:


All Articles