Sql-server – Can someone help me with the SQL Server syntax for the the Oracle query

bit manipulationoraclesql serverstored-procedurest-sql

Oracle version:

CREATE OR replace PROCEDURE Item_reco_recordset (p_depositid IN NUMBER, 
                                                     p_b1        IN NUMBER, 
                                                     p_itemset   OUT SYS_REFCURSOR) 
    AS 
    BEGIN 
        OPEN p_itemset FOR 
          SELECT * 
          FROM   pcwitem 
          WHERE  deposittransid = p_depositid 
                 AND ( ( Bitand(recostatecode, 1) > 0 
                         AND Bitand(recostatecode, 5) != 0 ) 
                        OR ( Bitand(recostatecode, 2) > 0 
                             AND Bitand(recostatecode, 6) != 0 ) 
                        OR ( p_b1 = 1 
                             AND Bitand(recostatecode, 3) > 0 
                             AND Bitand(recostatecode, 7) != 0 ) 
                        OR ( Bitand(recostatecode, 4) > 0 
                             AND Bitand(recostatecode, 8) != 0 ) 
                        OR ( Bitand(recostatecode, 9) > 0 
                             AND Bitand(recostatecode, 10) != 0 ) 
                        OR ( Bitand(recostatecode, 11) > 0 
                             AND Bitand(recostatecode, 15) != 0 ) 
                        OR ( Bitand(recostatecode, 12) > 0 ) 
                        OR ( Bitand(recostatecode, 16) > 0 
                             AND Bitand(recostatecode, 14) != 0 ) 
                        OR ( Bitand(recostatecode, 18) > 0 ) 
                        OR ( Bitand(recostatecode, 19) > 0 
                             AND Bitand(recostatecode, 20) != 0 ) 
                        OR ( Bitand(recostatecode, 21) > 0 ) 
                        OR ( Bitand(recostatecode, 22) > 0 ) 
                        OR ( Bitand(recostatecode, 23) > 0 
                             AND Bitand(recostatecode, 24) != 0 ) 
                        OR ( Bitand(recostatecode, 25) > 0 
                             AND Bitand(recostatecode, 29) != 0 ) 
                        OR ( Bitand(recostatecode, 26) > 0 ) 
                        OR ( Bitand(recostatecode, 28) > 0 ) 
                        OR ( Bitand(recostatecode, 31) > 0 ) 
                        OR ( Bitand(recostatecode, 32) > 0 
                             AND Bitand(recostatecode, 61) != 0 ) 
                        OR ( Bitand(recostatecode, 39) > 0 ) 
                        OR ( Bitand(recostatecode, 40) > 0 
                             AND Bitand(recostatecode, 64) != 0 ) 
                        OR ( Bitand(recostatecode, 48) > 0 ) 
                        OR ( Bitand(recostatecode, 51) > 0 ) 
                        OR ( Bitand(recostatecode, 53) > 0 
                             AND Bitand(recostatecode, 54) != 0 ) 
                        OR ( Bitand(recostatecode, 57) > 0 
                             AND Bitand(recostatecode, 58) != 0 ) ); 
    END item_reco_recordset;

SQL Server Version (throws error — bitand not a recognized built-in function):

IF OBJECT_ID('Item_reco_recordset', 'P') IS NOT NULL
  DROP PROCEDURE Item_reco_recordset;
GO

CREATE PROCEDURE Item_reco_recordset (@p_depositid FLOAT, 
                                                 @p_b1        FLOAT 
                                                ) 
AS 
BEGIN
SET NOCOUNT ON; 
      SELECT * 
      FROM   pcwitem 
      WHERE  deposittransid = @p_depositid 
             AND ( ( Bitand(recostatecode, 1) > 0 
                     AND Bitand(recostatecode, 5) != 0 ) 
                    OR ( Bitand(recostatecode, 2) > 0 
                         AND Bitand(recostatecode, 6) != 0 ) 
                    OR ( @p_b1 = 1 
                         AND Bitand(recostatecode, 3) > 0 
                         AND Bitand(recostatecode, 7) != 0 ) 
                    OR ( Bitand(recostatecode, 4) > 0 
                         AND Bitand(recostatecode, 8) != 0 ) 
                    OR ( Bitand(recostatecode, 9) > 0 
                         AND Bitand(recostatecode, 10) != 0 ) 
                    OR ( Bitand(recostatecode, 11) > 0 
                         AND Bitand(recostatecode, 15) != 0 ) 
                    OR ( Bitand(recostatecode, 12) > 0 ) 
                    OR ( Bitand(recostatecode, 16) > 0 
                         AND Bitand(recostatecode, 14) != 0 ) 
                    OR ( Bitand(recostatecode, 18) > 0 ) 
                    OR ( Bitand(recostatecode, 19) > 0 
                         AND Bitand(recostatecode, 20) != 0 ) 
                    OR ( Bitand(recostatecode, 21) > 0 ) 
                    OR ( Bitand(recostatecode, 22) > 0 ) 
                    OR ( Bitand(recostatecode, 23) > 0 
                         AND Bitand(recostatecode, 24) != 0 ) 
                    OR ( Bitand(recostatecode, 25) > 0 
                         AND Bitand(recostatecode, 29) != 0 ) 
                    OR ( Bitand(recostatecode, 26) > 0 ) 
                    OR ( Bitand(recostatecode, 28) > 0 ) 
                    OR ( Bitand(recostatecode, 31) > 0 ) 
                    OR ( Bitand(recostatecode, 32) > 0 
                         AND Bitand(recostatecode, 61) != 0 ) 
                    OR ( Bitand(recostatecode, 39) > 0 ) 
                    OR ( Bitand(recostatecode, 40) > 0 
                         AND Bitand(recostatecode, 64) != 0 ) 
                    OR ( Bitand(recostatecode, 48) > 0 ) 
                    OR ( Bitand(recostatecode, 51) > 0 ) 
                    OR ( Bitand(recostatecode, 53) > 0 
                         AND Bitand(recostatecode, 54) != 0 ) 
                    OR ( Bitand(recostatecode, 57) > 0 
                         AND Bitand(recostatecode, 58) != 0 ) ); 
END; item_reco_recordset;

Best Answer

I have a sql server query here that gives me the current user options. It uses a bitwise AND, which in sql server is &.

    declare @UserOption int
    select @UserOption=convert(int, c.value) 
from sys.configurations c where c.name='user options'

    SELECT
    CAST(@UserOption & 1 AS bit) AS [DisableDefaultConstraintCheck],
    CAST(@UserOption & 2 AS bit) AS [ImplicitTransactions],
    CAST(@UserOption & 4 AS bit) AS [CursorCloseOnCommit],
    CAST(@UserOption & 8 AS bit) AS [AnsiWarnings],
    CAST(@UserOption & 16 AS bit) AS [AnsiPadding],
    CAST(@UserOption & 32 AS bit) AS [AnsiNulls],
    CAST(@UserOption & 64 AS bit) AS [AbortOnArithmeticErrors],
    CAST(@UserOption & 128 AS bit) AS [IgnoreArithmeticErrors],
    CAST(@UserOption & 256 AS bit) AS [QuotedIdentifier],
    CAST(@UserOption & 512 AS bit) AS [NoCount],
    CAST(@UserOption & 1024 AS bit) AS [AnsiNullDefaultOn],
    CAST(@UserOption & 2048 AS bit) AS [AnsiNullDefaultOff],
    CAST(@UserOption & 4096 AS bit) AS [ConcatenateNullYieldsNull],
    CAST(@UserOption & 8192 AS bit) AS [NumericRoundAbort],
    CAST(@UserOption & 16384 AS bit) AS [AbortTransactionOnError]

More information on the link below:

How does the operator “&” work in sql server?

Related Question