CODEWITHSUNDEEP
sql-servert-sqlcommon-table-expression
R. Salehi
R. Salehi

Reputation: 183

Set and define variables inside CTE (With) and use them in another CTE

Is there any way to define a variable inside a WITH expression like this? 

declare @Var1
declare @Var2;

With CTE as ( Select ...),
CTE2 as (select 
       CTE.Something,
       @Var1=(Select .. from CTE),
       @Var1=(Select .. from CTE)
    From CTE
)
Select * from CTE2

Upvotes: 2

Views: 9197

Answers (5)

Jenna Leaf
Jenna Leaf

Reputation: 2462

The official documentation of WITH CTE's is on Microsoft SQL Docs

WITH [CTE2] AS
(
   SELECT dbo.yourFunction1(t.column_i) as var1
   , dbo.yourFunction2(t.column_j) as var2
   FROM dbo.[CTE] t
   WHERE yourFunctionX(t.column_p) = 100
     AND t.column_q IS NOT NULL 
);

declare @var1 varchar(500), @var2 varchar(500);
Select @var1 = t2.var1, @var2 = t2.var2 from [CTE2] t2;

Upvotes: 0

Kelevra
Kelevra

Reputation: 126

There's no way you can set a variable in a CTE

If you specify your requirements we might find better solution

However my suggestion is not to use CTE but go with temp table and work from there

declare @Var1
declare @Var2;

Select *
Into #TEMP
from MyTable

Select @var1 = col1,
       @var2 = col2
from #TEMP

Select *, @var1, @var2
from #TEMP

Upvotes: 1

EzLo
EzLo

Reputation: 14199

No, you can't. The DOCS for the WITH common_table_expression says:

[ WITH <common_table_expression> [ ,...n ] ]  

<common_table_expression>::=  
    expression_name [ ( column_name [ ,...n ] ) ]  
    AS  
    ( CTE_query_definition )

And the CTE_query_definition is (emphasis mine):

Specifies a SELECT statement whose result set populates the common table expression. The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE cannot define another CTE. For more information, see the Remarks section and CREATE VIEW (Transact-SQL).

In a view you can't set variables, so you won't be able to set them in a CTE also.

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82504

Your code looks like T-SQL.

In T-SQL, you can't return a result set and set variables value in the same select statement, cte or not.

The following code is invalid in T-SQL:

declare @Var1 int;

select someColumn,
       @var1 = someOtherColumn -- or a subquery or whatever
from someTable

And it would still be invalid if the select statement was a part of a common table expression.

Also, a common table expression must contain a select statement that returns a result set - so you can't do something like this:

with cte as
(
    select @var1 = someColumn
    from someTable
)

Upvotes: 3

DarkRob
DarkRob

Reputation: 3833

You cannot select and set in single statement.

For your query you need to do this in 2 parts only. First to set your variable value. Second query to get the result record in table format.

declare @var int

--- First statement
; with cte as
(select ...)
select @var = (select .. from cte) 

--- second statement
; with cte as
(select ...)
, ct as (
select id, (select .. from cte) from cte
)
select * from ct

Although it is not required to set your variable value in the middle of your query.

You either get the values in variable or get the data in select statement.

Upvotes: 0

Related Questions