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 (http://blog.SQLAuthority.com)

About these ads

11 thoughts on “SQL SERVER – Dynamic Case Statement – FIX : ERROR 156 : Incorrect syntax near the keyword

  1. 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

  2. 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.

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #018 | SQL Server Journey with SQL Authority

  4. 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.

  5. create table Auto as AutomobileDB
    getting error — Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘as ‘

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s