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.