Declaring variable in WITH expression (SQL Server)?

Question

is there a way to define a variable inside a WITH expression? Something like:

;WITH SomeName AS ( 
    declare @somevar decimal
    set @somevar = ...
    SELECT ....)

Possible?

Answer 1

No, you would need to declare / set it outside of the CTE

DECLARE @somevar DECIMAL
;WITH SomeName AS (SELECT....)
SELECT @somevar = ...
FROM SomeName

Per MSDN:

CTE_query_definition
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.

Answer 2

I've had to use the following approach a few times as a work-around (for example using R-Server in t-sql as the input_data_1 expression; see https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/quickstart-r-inputs-and-outputs?view=sql-server-2017). The following illustrates the example:

WITH ctename (varname) as (
    SELECT MAX(somevar) FROM sometable
)
SELECT *
    FROM someothertable a
    JOIN ctename b on 1=1
    WHERE a.col1 > b.varname;