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

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
Previous Post
SQL SERVER – SELECT 1 vs SELECT * – An Interesting Observation
Next Post
SQL SERVER – Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial

Related Posts

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
  • 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
  • DESC my_contact;

    ERROR:-
    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘DESC’.

    Reply
  • 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
  • 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
  • create table Auto as AutomobileDB
    getting error — Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘as ‘

    Reply
  • 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
  • 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
  • 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

Leave a Reply