September 20, 2011 at 5:45 am
SQL Kiwi (9/20/2011)
Brandie Tarvin (9/20/2011)
CTEs (i.e. ;WIth () ... SELECT ) are single statements in this context, yes?Yes - most solutions to past challenges use CTEs extensively. Some would say to the point of being daft about it.
How else do you expect it to be with that rule :-D.
September 20, 2011 at 5:52 am
Brandie Tarvin (9/20/2011)
CTEs (i.e. ;WIth () ... SELECT ) are single statements in this context, yes?
<pedantic>
CTEs don't start with a ';', no more than an english sentence starts with a '.'.
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
September 20, 2011 at 5:55 am
GilaMonster (9/20/2011)
Brandie Tarvin (9/20/2011)
CTEs (i.e. ;WIth () ... SELECT ) are single statements in this context, yes?<pedantic>
CTEs don't start with a ';', no more than an english sentence starts with a '.'.
That's true only for the first phrase in any documents / books :-D.
And then again, the last thing you read ended with a period.
/pedantic :hehe:.
September 20, 2011 at 6:00 am
Ninja's_RGR'us (9/20/2011)
That's true only for the first phrase in any documents / books :-D.And then again, the last thing you read ended with a period.
/pedantic :hehe:.
.Really?
.So this would be considered good, grammatically correct english? .I doubt a grammar checker or editor would ever tolerate this, but it's what we're essentially doing by starting statements with statement terminator
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
September 20, 2011 at 6:02 am
GilaMonster (9/20/2011)
Ninja's_RGR'us (9/20/2011)
That's true only for the first phrase in any documents / books :-D.And then again, the last thing you read ended with a period.
/pedantic :hehe:.
.Really?
.So this would be considered good, grammatically correct english? .I doubt a grammar checker or editor would ever tolerate this, but it's what we're essentially doing by starting statements with statement terminator
Not the same context :-). Hence pedantic :-P.
September 20, 2011 at 6:02 am
Ninja's_RGR'us (9/20/2011)
Try doing ;with () Without any select after the with and you'll see if the engine thinks the statement is complete ;-).
Ok:
WITH CHANGE_TRACKING_CONTEXT (0x)
MERGE master.dbo.spt_values AS A USING (VALUES(NULL)) AS V(v) ON NULL = NULL
WHEN MATCHED THEN DELETE;
No SELECT there. I'm with Gail on the semicolon by the way.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 20, 2011 at 6:06 am
SQL Kiwi (9/20/2011)
Ninja's_RGR'us (9/20/2011)
Try doing ;with () Without any select after the with and you'll see if the engine thinks the statement is complete ;-).Ok:
WITH CHANGE_TRACKING_CONTEXT (0x)
MERGE master.dbo.spt_values AS A USING (VALUES(NULL)) AS V(v) ON NULL = NULL
WHEN MATCHED THEN DELETE;
No SELECT there. I'm with Gail on the semicolon by the way.
I was going more for select or dml.
I see both where you are going with this and I agree. Just doing Devil's advocate (need 300 posts for Steve, remember?)
September 20, 2011 at 6:10 am
Ninja's_RGR'us (9/20/2011)
I was going more for select or dml.
MERGE will be very upset to hear it's not DML :pinch:
But yes, I know what you had in mind - it just seemed fun to find something to fit the pattern you specified but did not include a SELECT. 300 posts, you see.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 20, 2011 at 6:11 am
GilaMonster (9/20/2011)
Brandie Tarvin (9/20/2011)
CTEs (i.e. ;WIth () ... SELECT ) are single statements in this context, yes?<pedantic>
CTEs don't start with a ';', no more than an english sentence starts with a '.'.
And yet the engine complains if I don't have any previous statement that have a ';' and don't put one before the WITH.
September 20, 2011 at 6:12 am
SQL Kiwi (9/20/2011)
Ninja's_RGR'us (9/20/2011)
Try doing ;with () Without any select after the with and you'll see if the engine thinks the statement is complete ;-).Ok:
WITH CHANGE_TRACKING_CONTEXT (0x)
MERGE master.dbo.spt_values AS A USING (VALUES(NULL)) AS V(v) ON NULL = NULL
WHEN MATCHED THEN DELETE;
No SELECT there. I'm with Gail on the semicolon by the way.
That was nasty! I know very few people that can read that query and guess what it does.
On the semicolon, I'm with Gail too.
-- Gianluca Sartori
September 20, 2011 at 6:16 am
SQL Kiwi (9/20/2011)
Ninja's_RGR'us (9/20/2011)
I was going more for select or dml.MERGE will be very upset to hear it's not DML :pinch:
But yes, I know what you had in mind - it just seemed fun to find something to fit the pattern you specified but did not include a SELECT. 300 posts, you see.
Obviously I wasn't clear in my writing (for a change :-D).
Here's what I had in mind with "select or dml, or whatever!".
BEGIN TRAN
;WITH CTE (object_id, name)
AS
(SELECT object_id, name FROM sys.objects)
--SELECT * FROM CTE
ROLLBACK
September 20, 2011 at 6:17 am
Brandie Tarvin (9/20/2011)
GilaMonster (9/20/2011)
Brandie Tarvin (9/20/2011)
CTEs (i.e. ;WIth () ... SELECT ) are single statements in this context, yes?<pedantic>
CTEs don't start with a ';', no more than an english sentence starts with a '.'.
And yet the engine complains if I don't have any previous statement that have a ';' and don't put one before the WITH.
Can't believe it.
Does this work? (my apologies for posting code on the Thread)
USE tempdb
GO
CREATE FUNCTION test()
RETURNS TABLE
AS
RETURN (
WITH sampleData (Num) AS (
SELECT 1
UNION ALL
SELECT 2
)
SELECT *
FROM sampleData
)
GO
SELECT * FROM dbo.test()
-- Gianluca Sartori
September 20, 2011 at 6:18 am
Brandie Tarvin (9/20/2011)
GilaMonster (9/20/2011)
Brandie Tarvin (9/20/2011)
CTEs (i.e. ;WIth () ... SELECT ) are single statements in this context, yes?<pedantic>
CTEs don't start with a ';', no more than an english sentence starts with a '.'.
And yet the engine complains if I don't have any previous statement that have a ';' and don't put one before the WITH.
The parser complains that the previous statement is not terminated with a ;, not that the with doesn't start with one.
Statements shouldn't start with statement terminators. Statements should end with statement terminators. Just like I don't start sentences with a ., I end them with one.
This is fine:
WITH SystemTables (object_id, index_id) AS (
SELECT object_id, index_id FROM sys.indexes AS i WHERE type = 2
)
SELECT * FROM SystemTables
ORDER BY index_id;
This is also fine:
SELECT COUNT(*) FROM sys.indexes AS i WHERE type = 2;
WITH SystemTables (object_id, index_id) AS (
SELECT object_id, index_id FROM sys.indexes AS i WHERE type = 2
)
SELECT * FROM SystemTables
ORDER BY index_id;
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
September 20, 2011 at 6:19 am
Gianluca Sartori (9/20/2011)
Brandie Tarvin (9/20/2011)
GilaMonster (9/20/2011)
Brandie Tarvin (9/20/2011)
CTEs (i.e. ;WIth () ... SELECT ) are single statements in this context, yes?<pedantic>
CTEs don't start with a ';', no more than an english sentence starts with a '.'.
And yet the engine complains if I don't have any previous statement that have a ';' and don't put one before the WITH.
Can't believe it.
Does this work? (my apologies for posting code on the Thread)
USE tempdb
GO
CREATE FUNCTION test()
RETURNS TABLE
AS
RETURN (
WITH sampleData (Num) AS (
SELECT 1
UNION ALL
SELECT 2
)
SELECT *
FROM sampleData
)
GO
SELECT * FROM dbo.test()
Works on 2K5 SP3-ish.
September 20, 2011 at 6:19 am
Gianluca Sartori (9/20/2011)
Does this work? (my apologies for posting code on the Thread)
USE tempdb
GO
CREATE FUNCTION test()
RETURNS TABLE
AS
RETURN (
WITH sampleData (Num) AS (
SELECT 1
UNION ALL
SELECT 2
)
SELECT *
FROM sampleData
)
GO
SELECT * FROM dbo.test()
Yes, that works absolutely fine.
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
Viewing 15 posts - 30,076 through 30,090 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply