Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cant define SQL variable when using WITH statement

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?

like image 983
Yosi Dahari Avatar asked Sep 14 '26 05:09

Yosi Dahari


2 Answers

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

like image 117
Martin Smith Avatar answered Sep 15 '26 20:09

Martin Smith


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

like image 33
Miller Avatar answered Sep 15 '26 19:09

Miller



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!