Declaring variable in WITH expression (SQL Server)?

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;

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.

Tags:

Sql Server