8 ms·

That variable support isn't part of SQL, it's part of the higher query execution language -- for want of a better term -- made available by the RDBMS. Besides e

That variable support isn't part of SQL, it's part of the higher query execution language -- for want of a better term -- made available by the RDBMS. Besides embedding them into a CTE or getting them from a predefined table(s), there's no way of getting arbitrary data (i.e., variables) into a SQL statement.

onion2k 11y ago
Local variable support is sort of part of SQL as it's part of the SQL/PSM Persistent Storage Module extension to SQL-92. Very few databases implement it according to the standard though.
- chris_wot 11y ago
  The key thing that is important is that a recursive CTE allows for the retrieval of data from graph-like table schemas. I'd sort of forgotten about this feature of SQL! But common table expressions and window functions actually keep the expressiveness of SQL and fill in gaps that just straight subselects, derived tables and joins alone can't always achieve. Take for instance the rolling update he highlights. Before SQL Server got windowing functions (and boy, were Microsoft ever slow in implementing those in SQL Server!) the way to do a reasonable fast query (i.e. Not use cursors) was one a guy called Jeff Moden came up with in 2012: http://www.sqlservercentral.com/articles/T-SQL/68467/ http://www.sqlservercentral.com/articles/T-SQL/68467/ It relied on undocumented behaviour he observed in cluster indexes - so long as he could keep the order of rows returned by the database, he figured out a very fast method of doing a running total. Obviously it was a hack and couldn't really be used, but it shows what folks can do when their favourite vendor doesn't supply critical features needed to process data in their favourite tool quickly :-)