how to negate the following SQL statement?

Question

I have the following conditional sql statement, which works.. but I have an empty statement in the if part... I want to negate it so that I can just use the if part with the logic

    IF @WorkPrescribed = 1 and (@DefectNo1 = @WorkPrescribedDefectNo or @DefectNo2 = @WorkPrescribedDefectNo or @DefectNo3 = @WorkPrescribedDefectNo or @DefectNo4 = @WorkPrescribedDefectNo)
  BEGIN
            -- I don't want this at all.. only reason I have this is so I can have the else condition
  END
    else
        BEGIN
             --My current logic is all here
        END

so I just want to know how to negate what I have, so I can do everything just with an If, rather than use the else

Thanks

score 4 · Accepted Answer · answered Sep 30 '13 at 09:42

4

Perhaps simply

IF NOT(@WorkPrescribed = 1 and (@DefectNo1 = @WorkPrescribedDefectNo or @DefectNo2 = @WorkPrescribedDefectNo or @DefectNo3 = @WorkPrescribedDefectNo or @DefectNo4 = @WorkPrescribedDefectNo))

answered Sep 30 '13 at 09:42

Tim Schmelter

450,073
74
686
939

score 1 · Answer 2 · answered Sep 30 '13 at 09:42

You could simply negate the logic by enclosing with a NOT(), or you could write like this:

IF @WorkPrescribed != 1 or (@DefectNo1 != @WorkPrescribedDefectNo and @DefectNo2 != @WorkPrescribedDefectNo and @DefectNo3 != @WorkPrescribedDefectNo and @DefectNo4 != @WorkPrescribedDefectNo)
    BEGIN
         --My current logic is all here
    END

score 0 · Answer 3 · answered Sep 30 '13 at 09:42

Just put NOT "around" the statement to be negated:

IF NOT (@WorkPrescribed = 1 and (@DefectNo1 = @WorkPrescribedDefectNo or @DefectNo2 = @WorkPrescribedDefectNo or @DefectNo3 = @WorkPrescribedDefectNo or @DefectNo4 = @WorkPrescribedDefectNo))
  BEGIN
            -- I don't want this at all.. only reason I have this is so I can have the else condition
  END
    else
        BEGIN
             --My current logic is all here
        END

score 0 · Answer 4 · edited May 23 '17 at 11:57

0

You can use IF @WorkPrescribed <> 1 instead of = operator

check this post. Should I use != or <> for not equal in TSQL?

or you can use

IF NOT @WorkPrescribed = 1 and 
(@DefectNo1 = @WorkPrescribedDefectNo or 
 @DefectNo2 = @WorkPrescribedDefectNo or 
 @DefectNo3 = @WorkPrescribedDefectNo or 
 @DefectNo4 = @WorkPrescribedDefectNo)

edited May 23 '17 at 11:57

Community

1
1

answered Sep 30 '13 at 09:43

Arvinder

7
2

1

that should not negate the whole statement but only the first part `@WorkPrescribed = 1` – DrCopyPaste Sep 30 '13 at 10:19

score 0 · Answer 5 · answered Sep 30 '13 at 11:01

0

IF @WorkPrescribed <> 1 
or @WorkPrescribedDefectNo not in (@DefectNo1, @DefectNo2, @DefectNo3, @DefectNo4)
BEGIN
 -- code
END

answered Sep 30 '13 at 11:01

t-clausen.dk

43,517
12
59
92

how to negate the following SQL statement?

5 Answers5