15,936,887 members
See more:
I am gettinfg error while executing sql query "Msg 156, Level 15, State 1, Line 55
Incorrect syntax near the keyword 'NOT'."
SQL
```DECLARE @param int
SET @param = 1
Select col1,col2,col3 from table1
WHERE table1.ASOfDate = '05/24/2013' AND(
CASE WHEN @param = 1 THEN table1.CloseCode NOT IN (2,3,8)
WHEN @param = 2 THEN table1.CloseCode IN (2,3,8)
END)```

Posted
Updated 23-Apr-18 11:40am
v4

## Solution 1

I modified above query as below and it is working now.

DECLARE @param int
SET @param = 1
Select col1,col2,col3 from table1
WHERE
table1.ASOfDate = '05/24/2013' AND (
(@param = 1 AND table1.CloseCode NOT IN (2,3,8) ) OR
(@param = 2 AND table1.CloseCode IN (2,3,8)))

gvprabu 28-May-13 11:20am
Bruno Destro 14-Sep-15 15:08pm
This code works for me too.

Thanks for share the solution!

Bruno Destro

## Solution 2

Hi,

Check the following Script...
SQL
```-- Solution 1 : Try with Dynamic Query

DECLARE @SqlQuery VARCHAR(1000), @param int

SELECT @param = 1, @SqlQuery =''

IF @param = 1
BEGIN
SELECT @SqlQuery = 'Select col1,col2,col3 from table1 WHERE ASOfDate = '''05/24/2013''' AND CloseCode NOT IN (2,3,8)'
END
ELSE IF @param = 2
BEGIN
SELECT @SqlQuery = 'Select col1,col2,col3 from table1 WHERE ASOfDate = '''05/24/2013''' AND CloseCode IN (2,3,8)'
END
EXEC (@SqlQuery)

-- Solution 2 : Change your WHERE Condition
DECLARE @param int

SET @param = 1

Select col1,col2,col3 from table1
WHERE table1.ASOfDate = '05/24/2013'
AND( (@param = 1 AND CloseCode NOT IN (2,3,8)) OR
(@param = 2 AND CloseCode IN (2,3,8))
)```

Regards,
GVPrabu

## Solution 3

Also Solution Like CASE WHEN FOR IN OR NOT
DECLARE @param int
SET @param = 1
Select col1,col2,col3 from table1
WHERE table1.ASOfDate = '05/24/2013' AND( 1 =
CASE WHEN @param = 1 AND table1.CloseCode NOT IN (2,3,8) THEN 0
WHEN @param = 2 AND table1.CloseCode IN (2,3,8) THEN 0
ELSE 1 END )

## Solution 4

Hi,

I you are looking like multiple condition with column values matching means :
use the below method it will works.

SQL
```DECLARE @param int
SET @param = 1
Select col1,col2,col3 from table1
WHERE table1.ASOfDate = '05/24/2013' and
@param = 1 and table1.CloseCode NOT IN (2,3,8) or
@param = 2 and table1.CloseCode IN (2,3,8) or
@param = 3 and table1.CloseCode IN (8)```