CTE within a stored proc

Question

Post reply

CTE within a stored proc

mar.ko

Default port

Points: 1451
More actions
August 28, 2015 at 8:17 am

#302684

SQL Server 2014
If I place an EXEC call anywhere near the CTE statements, the stored proc will not compile.
It says "invalid syntax".
If I move the EXEC to the top of the procedure, it compiles fiine.
WTF ? Bug ?

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Are you ending your statements with semicolons?

Other than that, I would need to see some sample code to be sure what are you talking about.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323504 More actions · Answer 2

the command prior to WITH MyCTE AS(...

must be terminated with a semicolon. that's probably where the issue likes.

that issue makes a lot of people precede their [WITH] to feature a semi colon, which is annoying to purists, but it works:

;WITH MyCTE AS(...

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mar.ko Default port Points: 1451 More actions · Answer 3

I want the EXEC right after the CTE.....no can do.

I put a semicolon after the last paren of the CTE and that throws a syntax error.

Something strange is happening here.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

mar.ko (8/28/2015)
I want the EXEC right after the CTE.....no can do.
I put a semicolon after the last paren of the CTE and that throws a syntax error.
Something strange is happening here.

Post the code that has the error.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ed Wagner SSC Guru Points: 287024 More actions · Answer 5

Luis Cazares (8/28/2015)
mar.ko (8/28/2015)
I want the EXEC right after the CTE.....no can do.
I put a semicolon after the last paren of the CTE and that throws a syntax error.
Something strange is happening here.
Post the code that has the error.

The statement that contains the CTE needs to end with a query that uses the CTE. Like Luis said, please post the code; that should clear up the confusion.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

mar.ko Default port Points: 1451 More actions · Answer 6

Ed Wagner (8/28/2015)
The statement that contains the CTE needs to end with a query that uses the CTE. Like Luis said, please post the code; that should clear up the confusion.

Bingo - therein lies the problem. I was not aware of that restriction.

Honestly, that's kind of a dumb one....CTE's should persist for the duration of the procedure and be able to be referenced at any time.

Lowell SSC Guru Points: 323504 More actions · Answer 7

mar.ko (8/28/2015)
Ed Wagner (8/28/2015)
The statement that contains the CTE needs to end with a query that uses the CTE. Like Luis said, please post the code; that should clear up the confusion.
Bingo - therein lies the problem. I was not aware of that restriction.
Honestly, that's kind of a dumb one....CTE's should persist for the duration of the procedure and be able to be referenced at any time.

temp tables exist for the duration. that's the tool you want.

CTE's are just a convenient way to format/visualize a sub select.

you can't propagate a sub select twice without doing the same work twice.(two queries cannot use it separately)

if it needs to exist, throw it in a temp table.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

drew.allen SSC Guru Points: 76944 More actions · Answer 8

mar.ko (8/28/2015)
Ed Wagner (8/28/2015)
The statement that contains the CTE needs to end with a query that uses the CTE. Like Luis said, please post the code; that should clear up the confusion.
Bingo - therein lies the problem. I was not aware of that restriction.
Honestly, that's kind of a dumb one....CTE's should persist for the duration of the procedure and be able to be referenced at any time.

CTEs are part of the syntax of INSERT/UPDATE/DELETE/MERGE statements, just like a FROM or WHERE clause. There is no reason for them to persist outside of that scope, just as there is no reason for the FROM or WHERE clause to persist outside of that scope.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Lowell (8/28/2015)
the command prior to WITH MyCTE AS(...
must be terminated with a semicolon. that's probably where the issue likes.
that issue makes a lot of people precede their [WITH] to feature a semi colon, which is annoying to purists, but it works:
;WITH MyCTE AS(...

My biggest pet peeve! The semicolon is a terminator, not a begininator. It belongs at the end of statements not the beginning.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

mar.ko (8/28/2015)
Honestly, that's kind of a dumb one....CTE's should persist for the duration of the procedure and be able to be referenced at any time.

That's not what a CTE is. It's not a temp table or table variable, it's a named subquery. Nothing more It's a subquery, that instead of being specified in the FROM clause, is specified before the query and referenced by name. It's not an object, it's not persisted.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mar.ko Default port Points: 1451 More actions · Answer 11

Lynn Pettis (8/28/2015)
My biggest pet peeve! The semicolon is a terminator, not a begininator. It belongs at the end of statements not the beginning.

Exactly, another dumb rule.

I don't care what anyone says, CTE's are temporary views and should persist for the life of a stored proc.

I am sure Microsoft could have made that possible.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

mar.ko (8/28/2015)
I don't care what anyone says, CTE's are temporary views

They're not. They are named subqueries.

If you consider them temporary views, you're going to keep running into situations where they don't behave as you expect.

Could MS make temporary views? Yes, but that would be a different feature with different rules and behaviours.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

mar.ko (8/28/2015)
Lynn Pettis (8/28/2015)
My biggest pet peeve! The semicolon is a terminator, not a begininator. It belongs at the end of statements not the beginning.
Exactly, another dumb rule.
I don't care what anyone says, CTE's are temporary views and should persist for the life of a stored proc.
I am sure Microsoft could have made that possible.

Really? A dumb rule? Let's see, the MERGE statement must be terminated by a semicolon. IIRC, the THROW statement that was recently introduced also requires it be terminated by a semicolon. Looks to me that what once was optional, the semicolon terminating a SQL statement, is starting to be come mandatory. In fact don't be surprised if it does become mandatory. Best thing to do, get used to doing it now.

x SSC-Insane Points: 23660 More actions · Answer 14

GilaMonster (8/28/2015)
mar.ko (8/28/2015)
Honestly, that's kind of a dumb one....CTE's should persist for the duration of the procedure and be able to be referenced at any time.
That's not what a CTE is. It's not a temp table or table variable, it's a named subquery. Nothing more It's a subquery, that instead of being specified in the FROM clause, is specified before the query and referenced by name. It's not an object, it's not persisted.

I'm pretty sure the execution context for a stored procedure isn't persisted either 🙂