Having issue with implementing CTE in Stored Procedure. Please help!!

Question

Having issue with implementing CTE in Stored Procedure. Please help!!

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

Lynn Pettis (11/14/2014)
Jason-299789 (11/14/2014)
Gail/Lynn/Ed,
I don't disagree, and as Lynn states maybe if MS enforced it so that code wouldn't compile without the use of the statement terminators, like they do in C#/C++ their use would become second nature.
as it is code written without them runs, except in a few notable situations (Merge, and CTE's), so we developers forget that we need them.
If you were to look at most (even I fail at times) of my production code, I terminate all my SQL statements with a semicolon. It is a habit I try to instill in others I work with as well. The best way to do it is with code reviews (I know, what are those!) as you can ensure that statements are properly terminated and hopefully the developers will get used to using them. They can also start adding them to legacy code as it comes up for modifications due to changing requirements.

My code is the same, but it's a habit I brought with me from working in Oracle years ago. I also try to instill this in others and ever since the 2014 deprecation list included it, I have something more to lean on. Some listen, some don't. It's only their future workload they're impacting if they chose not to listen. Every time we touch something, we should make sure it's right, including requirements that are coming down the line.

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

GilaMonster (11/14/2014)
Jason-299789 (11/14/2014)
Gail/Lynn/Ed,
I don't disagree, and as Lynn states maybe if MS enforced it so that code wouldn't compile without the use of the statement terminators, like they do in C#/C++ their use would become second nature.
as it is code written without them runs, except in a few notable situations (Merge, and CTE's), so we developers forget that we need them.
They're going to. Eventually. The thing is, they have to put a change like that through the usual 2 (or more) version deprecation cycle because otherwise people would scream blue murder.
Here's a question for you though, if you keep on 'forgetting' to use them, what are you going to do when you upgrade to SQL Server 2018(ish) and suddenly every single stored proc, function, ad-hoc SQL statement won't work because ; are now mandatory?

Yes it's going to be a lot of work for the code that wasn't written properly. The same thing happened when they stopped allowing ORDER BY in a view without a TOP. I know it wasn't right before, but people did it anyway. Migration to SQL 2018-ish or any other major version requires lots and lots of testing and I'm sure it will be no exception when they finally make semicolons required. That's why they give such extreme notice - so people start doing it now.

That's the theory at least. People will still complain, no matter how much notice they're given or how widely advertised MS makes it.

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Jason-299789 (11/14/2014)
Koen Verbeeck (11/13/2014)
The CTE should be followed by a SELECT, INSERT, UPDATE or DELETE statement, not by an IF statement.
ps: the semicolon is a statement terminator, it has no business being there before WITH.
The problem is that 90% of developers don't use the ';' on every line of code, I know I don't, especially in a declaration area, so putting the ';' at the start of the WITH has started to become common practice.

90% of the people are wrong 😛

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

g.britton SSChampion Points: 13863 More actions · Answer 4

I may have missed something, but the only difference I can see in the IF...ELSE... clauses is this (in the else clause)

AND EnumKey != 'ClosingEntry' AND EnumKey != 'ClosingEntryRevision'

That's a lot of repeated code and a DRY violation. Instead, put the test for @IsClosingEntry = 1 in the WHERE clause

.. AND (@IsClosingEntry = 1 AND EnumKey != 'ClosingEntry' AND EnumKey != 'ClosingEntryRevision' OR @IsClosingEntry <> 1) AND ( ...other conditions...)

Gerald Britton, Pluralsight courses

cphite SSCarpal Tunnel Points: 4278 More actions · Answer 5

Ed Wagner (11/14/2014)
GilaMonster (11/14/2014)
Jason-299789 (11/14/2014)
Gail/Lynn/Ed,
I don't disagree, and as Lynn states maybe if MS enforced it so that code wouldn't compile without the use of the statement terminators, like they do in C#/C++ their use would become second nature.
as it is code written without them runs, except in a few notable situations (Merge, and CTE's), so we developers forget that we need them.
They're going to. Eventually. The thing is, they have to put a change like that through the usual 2 (or more) version deprecation cycle because otherwise people would scream blue murder.
Here's a question for you though, if you keep on 'forgetting' to use them, what are you going to do when you upgrade to SQL Server 2018(ish) and suddenly every single stored proc, function, ad-hoc SQL statement won't work because ; are now mandatory?
Yes it's going to be a lot of work for the code that wasn't written properly. The same thing happened when they stopped allowing ORDER BY in a view without a TOP. I know it wasn't right before, but people did it anyway. Migration to SQL 2018-ish or any other major version requires lots and lots of testing and I'm sure it will be no exception when they finally make semicolons required. That's why they give such extreme notice - so people start doing it now.
That's the theory at least. People will still complain, no matter how much notice they're given or how widely advertised MS makes it.

So, just out of curiosity, is there any reason for the semicolon terminator beyond it being arbitrarily deemed the "proper" way to code? I've been writing T-SQL for twenty years and I've never found a reason beyond that. I support multiple Microsoft products (Dynamics CRM, Great Plains) that are heavy with SQL code that doesn't use the semicolon terminator...

What purpose does making it mandatory actually serve?

g.britton SSChampion Points: 13863 More actions · Answer 6

cphite (11/14/2014)
Ed Wagner (11/14/2014)
GilaMonster (11/14/2014)
Jason-299789 (11/14/2014)
Gail/Lynn/Ed,
I don't disagree, and as Lynn states maybe if MS enforced it so that code wouldn't compile without the use of the statement terminators, like they do in C#/C++ their use would become second nature.
as it is code written without them runs, except in a few notable situations (Merge, and CTE's), so we developers forget that we need them.
They're going to. Eventually. The thing is, they have to put a change like that through the usual 2 (or more) version deprecation cycle because otherwise people would scream blue murder.
Here's a question for you though, if you keep on 'forgetting' to use them, what are you going to do when you upgrade to SQL Server 2018(ish) and suddenly every single stored proc, function, ad-hoc SQL statement won't work because ; are now mandatory?
Yes it's going to be a lot of work for the code that wasn't written properly. The same thing happened when they stopped allowing ORDER BY in a view without a TOP. I know it wasn't right before, but people did it anyway. Migration to SQL 2018-ish or any other major version requires lots and lots of testing and I'm sure it will be no exception when they finally make semicolons required. That's why they give such extreme notice - so people start doing it now.
That's the theory at least. People will still complain, no matter how much notice they're given or how widely advertised MS makes it.
So, just out of curiosity, is there any reason for the semicolon terminator beyond it being arbitrarily deemed the "proper" way to code? I've been writing T-SQL for twenty years and I've never found a reason beyond that. I support multiple Microsoft products (Dynamics CRM, Great Plains) that are heavy with SQL code that doesn't use the semicolon terminator...
What purpose does making it mandatory actually serve?

Conformance to the standard and to some degree, portability to systems that always require semicolons (e.g. mysql). There are at least two statements that require semicolon terminators in SQL Server: MERGE and THROW.

Gerald Britton, Pluralsight courses

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

GilaMonster (11/14/2014)
Jason-299789 (11/14/2014)
MS could help the situation by having its tools like SSMS and SSDT require it at compile time.
SSMS doesn't have a T-SQL compiler. When you ask for a piece of T-SQL to be parsed, SSMS sends that T-SQL to the connected SQL Server with a NOEXEC setting.
All SSMS has is a very basic syntax checker that's incredibly easy to confuse (red scwiggly).

That is true, but it is remarkably easy to write an add in for SSMS/SSDT that gives visual cues whenever a statement is missing a semicolon terminator, and also very easy to have that add in enforce their use if you wanted it to, just as Jason would like...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

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

cphite (11/14/2014)
What purpose does making it mandatory actually serve?

Simplifies the parser, makes it easier to extend the language. T-SQL's getting to the point where it's hard for the parser to tell statements apart consistently, in all cases. Hence the increasing requirement for ; to delimit statements. Less parsing bugs, easier to add new language features.

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

cphite SSCarpal Tunnel Points: 4278 More actions · Answer 9

GilaMonster (11/15/2014)
cphite (11/14/2014)
What purpose does making it mandatory actually serve?
Simplifies the parser, makes it easier to extend the language. T-SQL's getting to the point where it's hard for the parser to tell statements apart consistently, in all cases. Hence the increasing requirement for ; to delimit statements. Less parsing bugs, easier to add new language features.

Fair enough... just hoping they have a good tool for updating existing code... I have a LOT of code... 😀

I've decided to (try to) adopt the trailing semicolon... it still feels weird.

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Koen Verbeeck (11/14/2014)
Jason-299789 (11/14/2014)
Koen Verbeeck (11/13/2014)
The CTE should be followed by a SELECT, INSERT, UPDATE or DELETE statement, not by an IF statement.
ps: the semicolon is a statement terminator, it has no business being there before WITH.
The problem is that 90% of developers don't use the ';' on every line of code, I know I don't, especially in a declaration area, so putting the ';' at the start of the WITH has started to become common practice.
90% of the people are wrong 😛

And 75% of statistics are made up! 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 11

Sean Lange (11/19/2014)
Koen Verbeeck (11/14/2014)
Jason-299789 (11/14/2014)
Koen Verbeeck (11/13/2014)
The CTE should be followed by a SELECT, INSERT, UPDATE or DELETE statement, not by an IF statement.
ps: the semicolon is a statement terminator, it has no business being there before WITH.
The problem is that 90% of developers don't use the ';' on every line of code, I know I don't, especially in a declaration area, so putting the ';' at the start of the WITH has started to become common practice.
90% of the people are wrong 😛
And 75% of statistics are made up! 😉

12 in 14 scientific studies prove the fact that you can make any claim, as long as you use precise numbers. 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

cphite (11/19/2014)
GilaMonster (11/15/2014)
cphite (11/14/2014)
What purpose does making it mandatory actually serve?
Simplifies the parser, makes it easier to extend the language. T-SQL's getting to the point where it's hard for the parser to tell statements apart consistently, in all cases. Hence the increasing requirement for ; to delimit statements. Less parsing bugs, easier to add new language features.
Fair enough... just hoping they have a good tool for updating existing code...

Look in the mirror, there's your good tool. 🙂

Seriously, start updating your code, as you change a proc, go through it and add the ;. Do that consistently and by the time it becomes mandatory (whenever that is), there won't be much to change.

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

cphite SSCarpal Tunnel Points: 4278 More actions · Answer 13

I have several hundred reports that I maintain over various systems... many of them based on one or more stored procedures, many others with one or more SQL scripts embedded in the .RDL that rarely change and that I have no reason to touch other than to deal with this particular issue. It's going to be a ton of additional work.

So yeah, I get what you're saying and that was pretty much what I had in mind... but it's still going to be a ton of additional work. There is a lot of code that I or my team doesn't necessarily have a reason to touch other than to add semicolons.

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

cphite (11/19/2014)
I have several hundred reports that I maintain over various systems... many of them based on one or more stored procedures, many others with one or more SQL scripts embedded in the .RDL that rarely change and that I have no reason to touch other than to deal with this particular issue. It's going to be a ton of additional work.
So yeah, I get what you're saying and that was pretty much what I had in mind... but it's still going to be a ton of additional work. There is a lot of code that I or my team doesn't necessarily have a reason to touch other than to add semicolons.

Gail didn't suggest changing these for the sake of adding semicolons. She suggested that when you have to change existing code that you should also add semicolons as part of that update. Make that part of your ongoing process when you update code and it will minimize the effort when it becomes mandatory.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

cphite SSCarpal Tunnel Points: 4278 More actions · Answer 15

Sean Lange (11/19/2014)
cphite (11/19/2014)
I have several hundred reports that I maintain over various systems... many of them based on one or more stored procedures, many others with one or more SQL scripts embedded in the .RDL that rarely change and that I have no reason to touch other than to deal with this particular issue. It's going to be a ton of additional work.
So yeah, I get what you're saying and that was pretty much what I had in mind... but it's still going to be a ton of additional work. There is a lot of code that I or my team doesn't necessarily have a reason to touch other than to add semicolons.
Gail didn't suggest changing these for the sake of adding semicolons. She suggested that when you have to change existing code that you should also add semicolons as part of that update. Make that part of your ongoing process when you update code and it will minimize the effort when it becomes mandatory.

My point is, I have an awful lot of code that I will, in all likelihood, never touch as part of any ongoing process. I have report scripts that haven't changed in years - heck, there is code that hasn't been touched since before I started, because there's never been a need to touch it.

Which means that at some point, before these things become mandatory, I am going to have to update hundreds and hundreds of scripts that I would otherwise never touch. Sure, it's going to be spread over a couple of years at least - but there's gonna be a lot of it.

For example, we run a Microsoft CRM system that is heavily customized. Even assuming that MS does a bang-up job of reformatting all of the SQL that comes out of the box with CRM, we have literally hundreds of views, stored procedures, and functions that we've written ourselves; and those obviously won't be updated by MS.

The same is true for our Great Plains databases (we have several) and various other systems, both third party and developed in-house. We have mountains of code that simply doesn't change very often.

So yes, I get what Gail is saying and I don't disagree... I'm already making it a part of our ongoing process. But in our case, even minimized as Gail is suggesting leaves a massive amount of work to do.

And I get it - it's part of the job. We'll make it happen. But some automated help would be nice 😉