Click here to Skip to main content
15,936,887 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
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)


Please help me on that.
Posted
Updated 23-Apr-18 11:40am
v4

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)))
 
Share this answer
 
Comments
gvprabu 28-May-13 11:20am    
you only asked the Question, they u posted the answer...
If you know the Answer then why u asked this.?
Bruno Destro 14-Sep-15 15:08pm    
This code works for me too.

Thanks for share the solution!

Bruno Destro
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
 
Share this answer
 
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 )
 
Share this answer
 
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)
 
Share this answer
 
Comments
Patrice T 23-Apr-18 18:08pm    
5 years too late.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900