August 18, 2008 at 9:48 am
Why does the usage of a CTE give these errors ?
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
use tempdb
go
create table #ids (id int not null )
insert into #ids( id)
with MyCTE as
(select id from sysobjects)
select id from MyCTE
Having a redirection by having the CTE in a view works:
create view ViewWithCTE as
with MyCTE as
(select id from sysobjects)
select id from MyCTE
go
create table #ids (id int not null )
insert into #ids( id)
select id from ViewWithCTE
How about 2 CTEs in a single SQL Statement?
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
with ObjectsCTE as
(select id from sysobjects)
with ColumnsCTE
(select id , colid from syscolumns)
select *
from ObjectsCTE
join ColumnsCTE
on ColumnsCTE.id = ObjectsCTE.id
SQL = Scarcely Qualifies as a Language
August 18, 2008 at 10:02 am
Your syntax is wrong, the CTE comes at the beginning of the statement:
[font="Courier New"]use tempdb
go
create table #ids (id int not null )
; with MyCTE as
(select id from sysobjects)
insert into #ids( id)
select id from MyCTE[/font]
For 2 CTE's in a single statement, don't use 2 WITH's, use a comma before the second CTE definition.
Check out BOL for more information.
August 18, 2008 at 10:05 am
use tempdb
go
create table #ids (id int not null );
with MyCTE(id) as
(
select id from sysobjects
)
insert into #ids(id)
select id from MyCTE
April 11, 2012 at 1:09 am
it works!
April 11, 2012 at 1:12 am
Please note: Nearly 4 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2012 at 7:14 am
Anders put a semi-colon at the end of his CREATE statement.
I like to start the WITH with a semi-colon:
;WITH CTE AS (
...
)
Sometimes its needed but sometimes its not. This way I can't forget to put it on the prior statement.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 11, 2012 at 7:52 am
Semicolons are statement terminators, as such they should terminate statements, not start them. We don't start sentences in English with fullstops.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2012 at 7:59 am
GilaMonster (4/11/2012)
Semicolons are statement terminators, as such they should terminate statements, not start them. We don't start sentences in English with fullstops.
I agree with Gail. I have gotten into the habit (not always successful still) of terminating all statements with a semicolon (';'). As more advanced features are added to SQL Server that require the previous statement to be terminated, it just makes sense.
April 11, 2012 at 7:59 am
GilaMonster (4/11/2012)
Semicolons are statement terminators, as such they should terminate statements, not start them. We don't start sentences in English with fullstops.
Exactly.
When I started using CTEs (and didn't really understand them), I used to put semi-colons at the start due to the misinformation that is spread around about them. Now, it really irritates me when I see it in code anywhere near my applications.
April 11, 2012 at 8:52 am
GilaMonster (4/11/2012)
Semicolons are statement terminators, as such they should terminate statements, not start them. We don't start sentences in English with fullstops.
T-SQL is a completely different language from English with its own set of rules. The rules of English simply do not apply to T-SQL. There are also examples of human languages that DO start sentences with a statement terminator in some cases. (e.g., ¿Qué pasa?)
I have no problems with putting a semicolon before a WITH, but what I usually do is put the semicolon all by itself on the line right above the WITH, so that it's included if I need to move stuff around.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 11, 2012 at 6:16 pm
Gilamonster said:
Semicolons are statement terminators, as such they should terminate statements, not start them. We don't start sentences in English with fullstops.
Semicolons are referred to as statement terminators only because some stuffed shirt on the ANSI SQL formation committee decided to call them that! Really, you can think of it as a form of punctuation that is optional in many cases.
As Drew so accuratenly pointed out, there are many languages with prefixed punctuation marks. So unless you are a purist that must fully live between the lines, I say nomenclature be da*ned! Let us use what is most comfortable for each of us.
😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 11, 2012 at 6:25 pm
dwain.c (4/11/2012)
Gilamonster said:Semicolons are statement terminators, as such they should terminate statements, not start them. We don't start sentences in English with fullstops.
Semicolons are referred to as statement terminators only because some stuffed shirt on the ANSI SQL formation committee decided to call them that! Really, you can think of it as a form of punctuation that is optional in many cases.
As Drew so accuratenly pointed out, there are many languages with prefixed punctuation marks. So unless you are a purist that must fully live between the lines, I say nomenclature be da*ned! Let us use what is most comfortable for each of us.
😛
Well, the way things are going with SQL Server, don't be surprised if the use of statement terminators becomes mandatory with all statements.
April 11, 2012 at 7:40 pm
Well, the way things are going with SQL Server, don't be surprised if the use of statement terminators becomes mandatory with all statements.
Ugh! So much for upward compatibility!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply