CODEWITHSUNDEEP
sqlsql-servert-sql
FrenkyB
FrenkyB

Reputation: 7207

SQL Server condition not working

Below is part of a SQL Server query which is not working as expected. I don't know why, but condition falls through this part, like nothing is inserted (like all three conditions are set to NULL). What am I doing wrong here?

Input parameters for the stored procedure:

    @cStockPacked varchar(1) = NULL,
    @cWrittenDown varchar(1) = NULL,
    @cInPreparation varchar(1) = NULL

Part of the query which is not working:

(   
    (@cStockPacked IS NULL OR 
     (@cStockPacked = '1' AND MEST.MEST_STA = '1')
    )
    OR
    (@cInPreparation IS NULL OR 
     (@cInPreparation = '1' AND MEST.MEST_STA = '2')
    )
    OR
    (@cWrittenDown IS NULL OR 
     (@cWrittenDown = '1' AND MEST.MEST_STA = '4')
    )   
)

I've tried to hard code conditions, everything worked as expected:

(
    MEST.MEST_STA = '1' OR MEST.MEST_STA = '2' OR MEST.MEST_STA = '4'
)

I've tried with one condition (hardcoded), then with two and later with all three. All was good. I want to achieve the same result with query pasted above, but something is obviously wrong. What am I doing wrong?

Whole procedure:

ALTER PROCEDURE [dbo].[sp_CAMERC_HLP]
    (   , @cStockPacked varchar(1) = NULL
        , @cWrittenDown varchar(1) = NULL
        , @cInPreparation varchar(1) = NULL
    )
AS
BEGIN
    SELECT
        MEST.MEST_QUA, MERC_STA, MERC_NME,
        MERC_DES, MERC_NTO, MERC_UNI, MERC_LPR, 
        MERC.UNIT_KEY,
        COUNT_ALL_ROWS = COUNT(*) OVER()
    FROM 
        CAMERC MERC 
    INNER JOIN  
        CAMEGR CAMEGR ON MERC.MEGR_KEY = CAMEGR.MEGR_KEY
    INNER JOIN 
        CAMEST MEST ON MERC.MERC_KEY = MEST.MERC_KEY
    WHERE                                   
        (@cMERC_NME IS NULL OR MERC_NME LIKE '%' + @cMERC_NME + '%')
        AND
        (@iMEGR_KEY IS NULL OR MERC.MEGR_KEY IN (SELECT MEGR_KEY FROM CTE))         
        AND
        (@cMERC_CDO1 IS NULL OR MERC_CDO1 LIKE '%' + @cMERC_CDO1 + '%') 
        AND     
        and
        (   
            (@cStockPacked IS NULL OR @cStockPacked = '1' AND MEST.MEST_STA = '1')
            OR
            (@cInPreparation IS NULL OR (@cInPreparation = '1' AND MEST.MEST_STA = '2'))
            OR
            (@cWrittenDown IS NULL OR (@cWrittenDown = '1' AND MEST.MEST_STA = '4'))                
        )
END

Upvotes: 2

Views: 257

Answers (2)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

You may looking for this

(
  (   
    (@cStockPacked IS NOT NULL AND @cStockPacked = '1' AND MEST.MEST_STA = '1') OR
    (@cInPreparation IS NOT NULL AND @cInPreparation = '1' AND MEST.MEST_STA = '2') OR
    (@cWrittenDown IS NOT NULL AND @cWrittenDown = '1' AND MEST.MEST_STA = '4')    
  )
  OR
  ( @cStockPacked IS NULL OR @cInPreparation IS NULL OR @cWrittenDown IS NULL )
)

Upvotes: 3

IVNSTN
IVNSTN

Reputation: 9318

I don't understand why you accepted that answer as correct, it does not make much sense to test var for NULL with those conditions.

All you need - to make these conditions same as others. Just like this:

AND (@cMERC_NME IS NULL OR MERC_NME LIKE '%' + @cMERC_NME + '%')

update them to:

    AND (@cStockPacked IS NULL OR (@cStockPacked = '1' AND MEST.MEST_STA = '1'))
    AND (@cInPreparation IS NULL OR (@cInPreparation = '1' AND MEST.MEST_STA = '2'))
    AND (@cWrittenDown IS NULL OR (@cWrittenDown = '1' AND MEST.MEST_STA = '4'))

that's it.

Upvotes: 0

Related Questions