Error 156 Incorrect Syntax Near The Keyword Case
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or incorrect syntax near the keyword case sql server posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss
Incorrect Syntax Near The Keyword 'group'
Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only
Incorrect Syntax Near The Keyword 'select'
takes a minute: Sign up Incorrect syntax near 'case', 'when' and 'end' in CASE statement up vote 0 down vote favorite I been running into errors in my case statement. Right now I am testing it against NorthWind DB before
Incorrect Syntax Near The Keyword 'identity'
I use it in a project that I am involved in. I know I should be using IF/ELSE, and it works with the IF/ELSE, only problem is that I have to do this in a function and not use a stored procedure. From my understanding is that I have to use a case statement when using a tablevalued function, because functions can only contain be one statement so a case needs to be used and I was told that what I incorrect syntax near the keyword 'with'. if this statement is a common table expression was doing with the IF/ELSE is that functions don't allow that type of logic.. I'll be passing an ID along with the @PreAQ and its only going to return records based on filters that will be like the ones seen below, but with multiple LIKES. declare @PreAQ int set @PreAQ = 0 case when @PreAQ = 0 then (select * from Contacts where CompanyName like 'An%') when @PreAQ = 1 then (select * from Contacts where ContactTitle like 'S%') end The errors being thrown are Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'case'. Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'when'. Msg 102, Level 15, State 1, Line 14 Incorrect syntax near 'end'. I have also tried... case @PreAQ when = 0 then... and then I get the same errors EDIT Here is something similar to what my function will be doing at work (other than the filters are different and the table, but this is still using the NorthWind as a test DB, notice how depending on the '@PreAQ' number, I need it run the statement. I'm trying to do this... declare @PreAQ int set @PreAQ = 0 case when @PreAq 0 then (select * from [Contacts] where (CompanyName like 'An%' or CompanyName like 'B%' or CompanyName like 'Ch%' or CompanyName like 'De%' or CompanyName like 'F%')) when @PreAQ 1 then (select * from [Contacts] where (CompanyName
SERVER - Dynamic Case Statement - FIX : ERROR 156 : Incorrect syntax near the keyword February 28, incorrect syntax near the keyword 'order' 2008Pinal DaveSQL, SQL Performance, SQL Server, SQL Tips and incorrect syntax near the keyword 'declare' Tricks14 commentsOne of my friend sent me query asking me how to generate incorrect syntax near the keyword 'set' dynamic case statements in SQL. Every time he tries to run following query he is getting Error 156 : Incorrect syntax http://stackoverflow.com/questions/23878598/incorrect-syntax-near-case-when-and-end-in-case-statement near the keyword. He was frustrated with following two queries. There are two different ways to solve the problem when user want toIncorrect Query 1 : USE AdventureWorks GO DECLARE @OrderDirection VARCHAR(5) SET @OrderDirection = ‘DESC'SELECT * FROM Production.WorkOrder WHERE ProductID = 722 ORDER http://blog.sqlauthority.com/2008/02/28/sql-server-dynamic-case-statement-fix-error-156-incorrect-syntax-near-the-keyword/ 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 https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4fc3d649-eff9-403b-a3ff-c906955247e7/msg-156-level-15-state-1-line-3-incorrect-syntax-near-the-keyword-case?forum=sqlexpress Server > SQL Server Express Question 0 Sign in to vote First of http://www.sqlservercentral.com/Forums/Topic1030830-392-1.aspx all I am a novice with no training, I volunteer my time to assist charitable organizations. In the following situation B_RosterData1 is a sql view. I''ve tried researching the error message above but can't seem to find an answer. Following is the sql used to create B_RosterData1 : SELECT Id, Registered, System, [#], incorrect syntax Last, First, Address, City, Zip, BC, Phone, School, DOB, Weight, Grade, Verified, [Method Selected], Split, [Fathers Name], [Fathers Day Phone], [Fathers email], [Mothers Name], [Mothers Day Phone], [Mothers email], Date, Points, Alternate, Total, [Age Only], [Age Weight], [Grade Only], CASE WHEN ([B_RosterData].[Method Selected] = 'Age Only') THEN [B_RosterData].[Age Only] WHEN ([B_RosterData].[Method Selected] = 'Age Weight') THEN [B_RosterData].[Age Weight] WHEN ([B_RosterData].[Method Selected] = 'Grade incorrect syntax near Only') THEN [B_RosterData].[Grade Only] END AS [Level] FROM dbo.B_RosterData Following is the code I am trying to execute and get the error in the subject line: SELECT Id, [Level], Total, Points FROM B_RosterData1 case when ([Level] = 'Rookies' and [Points] > 10), then [102 - [Points] ) when ([Level] = 'Cubs' and [Points] between 11 and 23), then [127 - [Points] ) when ([Level] = 'Sophomore' and [Points] between 24 and 37), then [154 - [Points] ) when ([Level] = 'JV' and [Points] between 38 and 51), then [182 - [Points] ) when ([Level] = 'Varsity' and [Points] between > 51]), then [154 - [Points]) as [WK1] else 'Unlimit' I have tried several versions but get similar error messages. Any help I can get will more then appreciated. Thank you Billie Friday, February 10, 2012 8:29 AM Reply | Quote Answers 1 Sign in to vote Hello Billie, As the message says; in all case parts you return a float value, only in the else part you want to return a varchar data, such a mix of data types isn't possible. You may change it to: .... else -1 END AS [WK1] or to any ot
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 2008 » T-SQL (SS2K8) » Incorrect syntax near the keyword 'end'. Incorrect syntax near the keyword 'end'. Rate Topic Display Mode Topic Options Author Message Sandy2704Sandy2704 Posted Monday, December 6, 2010 2:03 PM Valued Member Group: General Forum Members Last Login: Tuesday, August 21, 2012 5:37 PM Points: 70, Visits: 485 I am getting the error message(Msg 156, Level 15, State 1, Procedure selectMediCalProcedureCode, Line 14Incorrect syntax near the keyword 'end'.) when i create the following stored procedurecreate procedure [dbo].[selectMediCalProcedureCode]@CodeID int asbeginset nocount onselect ProcedureIDfrom hrp_CrosswalkProcedureCodewhere Entity = 'Medi-Cal'and ProcedureID in( select *from cln_ServiceInstancewhere ProcedureCodeID = @CodeIDendI couldn't figure out where the errror message was....May be i m overlooking it....Can anyone help me solve this issue?? Post #1030830 parthi-1705parthi-1705 Posted Monday, December 6, 2010 2:30 PM Mr or Mrs. 500 Group: General Forum Members Last Login: Thursday, July 2, 2015 11:12 PM Points: 586, Visits: 2,196 Hiwhere ProcedureCodeID = @CodeID))-- is Missed Dont just post the code with out crossverfying it is simple and basic step, you should be able to solve. ThanksParthi ThanksParthi Post #1030848 David Webb-CDSDavid Webb-CDS Posted Monday, December 6, 2010 2:30 PM SSC Eights! Group: General Forum Members Last Login: Sunday, September 18, 2016 9:41 AM Points: 894, Visits: 8,574 Your 'in' clause has an opening paren but no closing. And then again, I might be wrong ...David Webb Post #1030849 GilaMonsterGilaMonster Posted Monday, December 6, 2010 2:39 PM SSC-Forever Group: General Forum Members Last Login: Today @ 6:00 AM Points: 45,371, Visits: 43,640 One other point.IN (SELECT * ....That will fail if