This sql is valid:
WITH A AS
(SELECT TOP 1000 *
FROM dbo.SomeTable)
SELECT * FROM A
While this one gives an error(Incorrect syntax near the keyword 'DECLARE'):
WITH A AS
(SELECT TOP 1000 *
FROM dbo.SomeTable)
DECLARE @dt DATETIME
SET @dt = GETDATE()
SELECT * FROM A
PRINT DATEDIFF(SS,GETDATE(),@dt)
Why?
Just do
DECLARE @dt DATETIME;
SET @dt = GETDATE();
WITH A
AS (SELECT TOP 1000 *
FROM dbo.SomeTable)
SELECT *
FROM A;
PRINT DATEDIFF(SS, GETDATE(), @dt);
The only valid thing following a CTE definition is a single statement using it
A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.
http://msdn.microsoft.com/en-us/library/ms175972.aspx
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With