Evaluating different conditions in one if statement on a stored proc
You can use.
IF ((@GARRISON != 'Netheravon' AND @GARRISON != 'Ludgershall') )
AND @INTERRUPT >= 5)
This can be shortened to
IF (@GARRISON NOT IN ('Netheravon', 'Ludgershall' )
AND @INTERRUPT >= 5)
The problem you had was that you have @variable IS NOT 'constant'
- this isnt valid SQL Syntax unless checking for null. For equality use =
, for non-equality use !=
or <>
(Both work IIRC).
Edit: This is the exact sctipt I used for testing, and works exactly as expected. SQL Server 2008
DECLARE @GARRISON nvarchar(255)
DECLARE @INTERRUPT INT
SET @GARRISON = 'Test'
SET @INTERRUPT = 10
IF (@GARRISON NOT IN ('Netheravon' ,'Ludgershall' )
AND @INTERRUPT >= 5)
BEGIN
SELECT 1
END
The above correctly outputs "1" as both conditions evaluate true. If I either change @INTERRUPT
to 4, or @GARRISON
to either Netheravon or Ludgershall then there is no output. This is the exact behaviour I expect.
Note: You can't have an empty BEGIN...END
block in SQL, so if that is empty you'll get the error Incorrect syntax near 'END'.
![GurdeepS](https://i.stack.imgur.com/VqpJT.png?s=256&g=1)
GurdeepS
Updated on June 26, 2022Comments
-
GurdeepS about 2 years
I have a stored proc I am trying to write , where I want to evaluate multiple conditions on the different parameters in one go. What is the best syntax for this?
Below is what I have:
AS BEGIN DECLARE @GARRISON nvarchar(255) DECLARE @ASSETTYPE nvarchar(255) DECLARE @FIVALUE int IF ( ( @GARRISON IS NOT 'Netheravon' AND @GARRISON IS NOT 'Ludgershall' ) AND @INTERRUPT >= 5) BEGIN END ELSE END