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)
14 Comments. Leave new
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.
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
Remove Values keyword and Opening and Closing bracket of the SELECT statement
DESC my_contact;
ERROR:-
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘DESC’.
Can you post the full code you used?
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.
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.
OPEN is a keyword. Use [OPEN]
create table Auto as AutomobileDB
getting error — Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘as ‘
This is invalid syntax. What are you trying to do?
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…
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
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’
What exactly you want to achieve?