SQL SERVER – Dynamic Case Statement – FIX : ERROR 156 : Incorrect syntax near the keyword

February 28, 2008

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

One of my friend sent me query asking me how to generate dynamic case statements in SQL. Every time he tries to run following query he is getting Error 156 : Incorrect syntax near the keyword. He was frustrated with following two queries. There are two different ways to solve the problem when user want to

Incorrect Query 1 :
USE AdventureWorks
GO
DECLARE @OrderDirection VARCHAR(5)
SET @OrderDirection = ‘DESC’

SELECT *
FROM Production.WorkOrder
WHERE ProductID = 722
ORDER BY OrderQty
CASE
WHEN
@OrderDirection = ‘DESC’
THEN
DESC
ELSE
ASC
END
GO
ResultSet:
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword ‘CASE’.

Incorrect Query 2 :
USE AdventureWorks
GO
DECLARE @OrderDirection VARCHAR(5)
SET @OrderDirection = ‘DESC’

SELECT *
FROM Production.WorkOrder
WHERE ProductID = 722
ORDER BY
CASE
WHEN
@OrderDirection = ‘DESC’
THEN
OrderQty DESC
ELSE
OrderQty ASC
END
GO
ResultSet:
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword ‘DESC’.

Correct Query 1 : Using CASE to OrderBy
USE AdventureWorks GO DECLARE @OrderDirection VARCHAR(5) SET @OrderDirection = 'DESC' SELECT * FROM Production.WorkOrder WHERE ProductID = 722 ORDER BY CASE WHEN @OrderDirection = 'DESC' THEN OrderQty END DESC, CASE WHEN @OrderDirection = 'ASC' THEN OrderQty END GO

Correct Query 2: Using CASE to Multiply with Negative Number
USE AdventureWorks GO DECLARE @OrderDirection VARCHAR(5) SET @OrderDirection = 'DESC' SELECT * FROM Production.WorkOrder WHERE ProductID = 722 ORDER BY OrderQty * CASE WHEN @OrderDirection = 'DESC' THEN -1 ELSE 1 END GO

You can use any of above mentioned correct method and use CASE statement to ORDER BY dynamically. If you are interested to learn how to ORDER BY dynamically more than one column you can read my previous article SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable.

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Error Messages, SQL Scripts

SQL SERVER – SELECT 1 vs SELECT * – An Interesting Observation

SQL SERVER – Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial

14 Comments. Leave new

Allan Svelmoe Hansen
February 28, 2008 12:13 pm
Simply send the integer value (1 for asc and -1 for desc) down to the query instead of a text string, then you can use that value to multiply without ever using a case in the order by.
Reply
Adars
February 23, 2010 5:32 pm
while trying to execute the following code
INSERT INTO [OPNSDATA].[dbo].[OPSDATA]
([POD_NO]
,[IOMNO]
,[IOMDT]
,[IOMDESTN]
,[IOMWT]
)
VALUES
( SELECT TRACKBASE.DBO.IOMMAST.POD_NO,TRACKBASE.DBO.IOMMAST.IOMNO,TRACKBASE.DBO.IOMMAST.SYS_DT AS IOMDT,TRACKBASE.DBO.IOMMAST.[WEIGHT] AS IOMWT,TRACKBASE.DBO.IOMMAST.DESTN AS IOMDESTN FROM TRACKBASE.DBO.IOMMAST WHERE TRACKBASE.DBO.IOMMAST.SYS_DT=DATEADD(“DAY”,-2,CONVERT(SMALLDATETIME,CONVERT( VARCHAR(12) ,GETDATE(),101))) AND TRACKBASE.DBO.IOMMAST.POD_NO LIKE ‘BLR%’)
GO
i got the error as follows
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword ‘SELECT’.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ‘)’.
can you help me
thanks
Reply
- Madhivanan
  August 25, 2010 2:28 pm
  Remove Values keyword and Opening and Closing bracket of the SELECT statement
  Reply
tushar
August 25, 2010 9:09 am
DESC my_contact;
ERROR:-
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘DESC’.
Reply
- Madhivanan
  August 25, 2010 2:26 pm
  Can you post the full code you used?
  Reply
sujatha
December 13, 2011 2:09 pm
Given question: supplier supplying only part s1
Supplier part
p1 s1
p1 s2
p2 s2
p3 s3
p3 s4
p4 s1
p4 s3
p5 s5
Answer which i did as show below.
I have created table ‘test’ and the query as shown below-
select supplier
from test
where part=’s1’and supplier not in (select supplier
from test
where part!=’s1′)
i want the still optimised answer.plz anyone help me.
Reply
nisha
November 15, 2013 9:05 pm
INSERT INTO database2.dbo.Charge
( __$start_lsn ,
__$end_lsn ,
__$seqval ,
__$operation ,
__$update_mask ,
ID ,
LoanID,
DueDate,
PaidDate,
OPEN,
Version,
ProcessingSequence
)
SELECT *
FROM database1.dbo.Charge
I am getting and error: Incorrect syntax near the keyword ‘OPEN’.
can anybody help me out with this? Thanks.
Reply
- madhivanan
  March 19, 2014 6:08 pm
  OPEN is a keyword. Use [OPEN]
  Reply
naN
March 4, 2014 12:27 pm
create table Auto as AutomobileDB
getting error — Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘as ‘
Reply
- madhivanan
  March 19, 2014 5:18 pm
  This is invalid syntax. What are you trying to do?
  Reply
Anil kumar
October 9, 2014 6:25 pm
While trying to Alter the stored proc I am getting this error
Msg 156, Level 15, State 1, Procedure GetExcludedContracts, Line 20
Incorrect syntax near the keyword ‘as’.
here is my script:
ALTER PROC [dbo].[GetExcludedContracts]
@DealerID varchar(10),
@CustomerOrVIN varchar(100)=null ,
@PageIndex int,
@PageSize int
AS
BEGIN
SELECT RLDetails.ContractNumber as ContractID ,EC.ContractPurchaseDate as ContractDate,
ISNULL(eC.FirstName + ‘ ‘ , ”) + ISNULL(eC.LastName,”) AS Name ,
eC.VehicleYear as [Year], eC.VehicleMake as Make,eC.VehicleModel as Model,eC.TermMiles as Miles,eC.PlanCode as Coverage,
‘Miles@Issues’ = eC.TermMiles, ‘Last 8 of VIN’= eC.VIN,eC.TermMonths AS Term,cast(round(eC.RetailCost,2) as numeric(36,2)) AS CustomerCost,
cast(round(eC.DealerCost,2) as numeric(36,2)) AS DealerTotal,RLDetails.LogID,RLDetails.PKID,RowNum
FROM
(
SELECT RLDetails.ContractNumber as ContractID ,EC.ContractPurchaseDate as ContractDate,
ISNULL(eC.FirstName + ‘ ‘ , ”) + ISNULL(eC.LastName,”) AS Name ,
eC.VehicleYear as [Year], eC.VehicleMake as Make,eC.VehicleModel as Model,eC.TermMiles as Miles,eC.PlanCode as Coverage,
‘Miles@Issues’ = eC.TermMiles, ‘Last 8 of VIN’= eC.VIN,eC.TermMonths AS Term,cast(round(eC.RetailCost,2) as numeric(36,2)) AS CustomerCost,
cast(round(eC.DealerCost,2) as numeric(36,2)) AS DealerTotal,RLDetails.LogID,RLDetails.PKID,ROW_NUMBER() OVER(ORDER BY Id as RowNum) from
[ExcludedContracts] RLDetails
INNER JOIN eFulfillment_DEV.dbo.eContracts EC ON EC.PKID=RLDetails.PKID
where EC.DealerID like ‘%’ + @DealerID
AND RLDetails.IsExcluded=1
AND (
eC.FirstName like ‘%’+@CustomerOrVIN+’%’ OR eC.LastName like ‘%’+@CustomerOrVIN+’%’
OR FirstName + ‘ ‘ + LastName Like ‘%’+@CustomerOrVIN+’%’
OR eC.VIN like ‘%’+@CustomerOrVIN+’%’
OR cast(EC.ContractPurchaseDate as varchar(10)) like ‘%’+@CustomerOrVIN+’%’
OR RLDetails.ContractNumber like ‘%’+@CustomerOrVIN+’%’
OR cast(eC.VehicleYear as varchar(10)) =@CustomerOrVIN
OR eC.VehicleMake like ‘%’+@CustomerOrVIN+’%’
OR eC.VehicleModel like ‘%’+@CustomerOrVIN+’%’
OR cast(eC.TermMiles as varchar(10)) =@CustomerOrVIN
OR eC.PlanCode like ‘%’+@CustomerOrVIN+’%’
OR cast(eC.TermMonths as varchar(10)) like ‘%’+@CustomerOrVIN+’%’
OR cast(eC.DealerCost as varchar(10)) like ‘%’+@CustomerOrVIN+’%’
OR cast(eC.RetailCost as varchar(10)) like ‘%’+@CustomerOrVIN+’%’
OR (ISNULL(@CustomerOrVIN,”) =”)
)
)as ContractDetails
where
RowNum between ((@PageIndex*10)+1) and ((@PageIndex+1)*@PageSize)
–order by EC.ContractPurchaseDate
END
Please help me…
Reply
Anil kumar
October 9, 2014 6:33 pm
while trying to Alter a stored proc I am getting the bellow error
Msg 156, Level 15, State 1, Procedure GetExcludedContracts, Line 20
Incorrect syntax near the keyword ‘as’.
here is my script:
Use [ABC]
Go
ALTER PROC [dbo].[GetExcludedContracts]
@DealerID varchar(10),
@CustomerOrVIN varchar(100)=null ,
@PageIndex int,
@PageSize int
AS
BEGIN
SELECT RLDetails.ContractNumber as ContractID ,EC.ContractPurchaseDate as ContractDate,
ISNULL(eC.FirstName + ‘ ‘ , ”) + ISNULL(eC.LastName,”) AS Name ,
eC.VehicleYear as [Year], eC.VehicleMake as Make,eC.VehicleModel as Model,eC.TermMiles as Miles,eC.PlanCode as Coverage,
‘Miles@Issues’ = eC.TermMiles, ‘Last 8 of VIN’= eC.VIN,eC.TermMonths AS Term,cast(round(eC.RetailCost,2) as numeric(36,2)) AS CustomerCost,
cast(round(eC.DealerCost,2) as numeric(36,2)) AS DealerTotal,RLDetails.LogID,RLDetails.PKID,RowNum
FROM
(
SELECT RLDetails.ContractNumber as ContractID ,EC.ContractPurchaseDate as ContractDate,
ISNULL(eC.FirstName + ‘ ‘ , ”) + ISNULL(eC.LastName,”) AS Name ,
eC.VehicleYear as [Year], eC.VehicleMake as Make,eC.VehicleModel as Model,eC.TermMiles as Miles,eC.PlanCode as Coverage,
‘Miles@Issues’ = eC.TermMiles, ‘Last 8 of VIN’= eC.VIN,eC.TermMonths AS Term,cast(round(eC.RetailCost,2) as numeric(36,2)) AS CustomerCost,
cast(round(eC.DealerCost,2) as numeric(36,2)) AS DealerTotal,RLDetails.LogID,RLDetails.PKID,ROW_NUMBER() OVER(ORDER BY Id as RowNum) from
[ExcludedContracts] RLDetails
INNER JOIN eFulfillment_DEV.dbo.eContracts EC ON EC.PKID=RLDetails.PKID
where EC.DealerID like ‘%’ + @DealerID
AND RLDetails.IsExcluded=1
AND (
eC.FirstName like ‘%’+@CustomerOrVIN+’%’ OR eC.LastName like ‘%’+@CustomerOrVIN+’%’
OR FirstName + ‘ ‘ + LastName Like ‘%’+@CustomerOrVIN+’%’
OR eC.VIN like ‘%’+@CustomerOrVIN+’%’
OR cast(EC.ContractPurchaseDate as varchar(10)) like ‘%’+@CustomerOrVIN+’%’
OR RLDetails.ContractNumber like ‘%’+@CustomerOrVIN+’%’
OR cast(eC.VehicleYear as varchar(10)) =@CustomerOrVIN
OR eC.VehicleMake like ‘%’+@CustomerOrVIN+’%’
OR eC.VehicleModel like ‘%’+@CustomerOrVIN+’%’
OR cast(eC.TermMiles as varchar(10)) =@CustomerOrVIN
OR eC.PlanCode like ‘%’+@CustomerOrVIN+’%’
OR cast(eC.TermMonths as varchar(10)) like ‘%’+@CustomerOrVIN+’%’
OR cast(eC.DealerCost as varchar(10)) like ‘%’+@CustomerOrVIN+’%’
OR cast(eC.RetailCost as varchar(10)) like ‘%’+@CustomerOrVIN+’%’
OR (ISNULL(@CustomerOrVIN,”) =”)
)
)as ContractDetails
where
RowNum between ((@PageIndex*10)+1) and ((@PageIndex+1)*@PageSize)
–order by EC.ContractPurchaseDate
END
How to resolve this. Please help me…
Regards,
Anil
Reply
poly
February 8, 2015 9:10 am
declare @dott date
declare @agee int
set @dott=’12/12/2014′
set @agee =DATEDIFF(YEAR,@dott,GETDATE())
case
when (MONTH(@dot)>MONTH(getdate()))or
(MONTH(@dot)=MONTH(getdate()) and day(@dot)>day(getdate()))
then 1
else 0
select @agee
is error Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword ‘CASE’
Reply
- Pinal Dave
  February 9, 2015 8:58 pm
  What exactly you want to achieve?
  Reply