[12.4.476 Pro] - Q: CTE intellisense

[12.4.476 Pro] - Q: CTE intellisense

How does CTE recognition work for SA intellisense? It has quite a few glitches.

If I have a CTE defined somewhere in the code outside a stored procedure it is recognized. But when I have it inside a CREATE/ALTER PROCEDURE part, it does not, even if there's nothing else in the editor. Typing the code inside that stored procedure SA suggests the CTE defined outside the procedure. When I remove the proc-outside CTE, it finds no CTEs at all.

Check this short video here.

I've run a few experiments, and I see that in some cases it suggests all the CTEs defined in the code somewhere above the current line (I guess that's why the popup had cte_1 inside the stored procedure). In contrast, it should only offer the ones defined since the last semicolon in the code immediately above.

It only delimits the name when it is picked from the tables and not when chosen from the local variables (though this is understandable). It also does not suggest CTEs in a CTE chain beyond the first.

Don't you need a semicolon in front/before WITH? My hypothesis it's likely recognized as BEGIN WITH... and then some unrelated SELECT ...

	SysOp wrote:
	Don't you need a semicolon in front/before WITH? My hypothesis it's likely recognized as BEGIN WITH... and then some unrelated SELECT ...

The requirement/limitation you're referring to is this:

	Quote:
	When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

But this does not apply here. The first CTE has no preceding statements, and neither has the one in the stored procedure. The only keyword before the CTE in the procedure is the BEGIN which isn't a statement but a block definer and it does not have to be closed by a semicolon. The only statement before the CTE in the stored procedure is the CREATE PROCEDURE itself but that does not count, as it does not precede the CTE statement but encapsulates it. The CTE is in the first statement of that batch. That was why I executed the ALTER PROCEDURE statement in the video so that it can be seen that it has valid syntax. As soon as you put a non-semicolon terminated statement in front of the CTE in the stored procedure, eg. a plain SELECT 1 would do, the ALTER statement will fail and you'll get an error:

	Quote:
	g 319, Level 15, State 1, Proc "usp_why", 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....

I also don't recall T-SQL in SQL Server having a "BEGIN WITH" keyword combo, that could be recognized as a construct that should separate the SELECT from its intended WITH.

I think you are technically correct and yet... WITH keyword can be used in dozens of different SQL statements, for example, ALTER INDEX...WITH reorg options, BULK INSERT ... WITH batch size, and many others. There is no simple way to guess that when you type WITH you are thinking of a CTE, and it's not a keyword in some other unfinished statement. In comparison the usual SELECt, DELETe, UPDATE, etc... are easily recognized as indicators of new statements or subqueries.

I didn't mean BEGIN WITH as a type of statement, I meant that they can be considered as keywords in other statements. There is much semantic ambiguity. The only unambiguous case for WITH starting a new statement is when it's separated by a batch delimiter or a statement delimiter, and so it's clearly the very first keyword. Which is why I think Intellisense doesn't work as you expect.

In your example, in the procedure compile time T-SQL compiler should understand the context of CREATE PROCEDURE and know that WITH starts new CTE.

Well, yes, it is amazing how the people that came up WITH (miserable pun intended) the thought that "let's introduce a new feature, and let it be known by re-using a keyword that is already overloaded to the death, how could that possibly go wrong, right?" could have anticipated that this is going to have some horrible consequences in the future, and they didn't. I understand that you need to do some pretty heavy lifting to analyze and identify statements and that this hideous WITH keyword does not make it easier in the slightest. This started bothering me ages ago, so I began adding semicolons to the end of statements in SQL Server even though it does not require it (another bad idea from MS). MSSQL was my first when I started learning SQL, and it "persuaded" me into the bad and lazy habit of omitting the semicolon right from the start. That habit started to have irritating side effects right from the moment I had to invade other shades of SQL.

Back to the topic. I was merely stating that the IntelliSense did not work, not only as I expected, but at all, no matter how hard I strived to nudge it in the correct way. While it was not in the video, I did try to provide all the assistance I could by prepending a semicolon right in front of the WITH keyword, but that did not work either. Let me show it in this short video here. Even after adding that semicolon, the popup still does not have that cte_2 in its list.

The code does look ugly with that "empty semicolon", but I'm sort of content with things that look ugly and work. Unfortunately, this isn't one of those.

It's very likely the reasons for introducing new features or syntax like CTE come from standards governing bodies which don't cater to specific database implementations and don't care about backward incompatibilities. CTE became standard in ANSI SQL-1999 standard, initial implementation came to SQL Server I believe in 2005, and Microsoft didn't get to choose which keywords to use for that.

Thank you very much for the second video, make it a lot clearer. I will submit a bug report with a link.

In order to dispel misunderstandings, my reference to the geniuses reusing 'WITH' as if they were running out of usable synonyms was directed toward those standards governing bodies. I had no intention to imply that Microsoft had anything to do with that. Their fault, on the other hand, is allowing statements without mandatory statement closing indicators (semicolon). Since not many other database dialect creators/providers thought this was a good idea, I highly doubt they were forced to let us be sloppy when writing code that is almost expected to cause issues when it comes to analyzing its structure due to its ambiguity, as in this case."

/rant off

and I'm sorry for any inconvenience caused by reading the lines of my frustration. Thank you very much for your patience, and for submitting the bug report!