CODEWITHSUNDEEP
sqlsql-server-2008common-table-expression
BenOfTheNorth
BenOfTheNorth

Reputation: 2872

SQL: Set variable during Select inside a CTE

I'm trying to set a variable value inside a CTE like so:

DECLARE @var INT;

WITH cte AS
(
    SELECT @var = column FROM ...
)

But I get the following error: Incorrect syntax near '='

Is this not possible to do, or does the syntax change when performed inside a CTE?

Upvotes: 4

Views: 7665

Answers (2)

M.Ali
M.Ali

Reputation: 69544

Not exactly assigning value to a variable but maybe you could do something like this....

DECLARE @T TABLE (ID INT, VALUE INT)
INSERT INTO @T VALUES (1,100),(2,200),(3,300),(4,400),(5,500)

;WITH Variable (A)    --<-- The value you wanted to store in a variable
AS
  ( 
    SELECT  AVG(VALUE)
    FROM @T
  ),
CTE (ID , Value)   --<-- Actual CTE where you wanted to use the value
AS  
  (
  SELECT * 
  FROM @T 
  WHERE VALUE > ( SELECT A      --<-- Use that value here 
                  FROM Variable)
  )
SELECT * 
FROM CTE

Upvotes: 1

Suyash Khandwe
Suyash Khandwe

Reputation: 396

CTE is more like a VIEW rather than a stored procedure. It doesn't seem to be correct syntax - since you can't set a variable inside a CTE - to the best of my knowledge.

Upvotes: 3

Related Questions