This is a perfectly valid SQL Query and works nicely (for e.g.) in Microsoft SQL Server 2014:
with t as (select 'a' as attr) select * from t it returns a, surely one can simplify it and drop the with part, but that’s not the point, the point is to find out a smallest example that illustrates the need/problem. I can pack part of it as a UDF:
CREATE PROCEDURE x(@__employee_id uniqueidentifier) AS RETURN (select 'a' as attr); How can I pack a query that has with block? My aim is to achieve something like:
CREATE PROCEDURE x (@__employee_id uniqueidentifier) AS RETURN ( with t as (select 'a' as attr) select * from t ); however sql server does not like it, firing an error:
Msg 156, Level 15, State 1, Procedure x, Line 4 [Batch Start Line 0] Incorrect syntax near the keyword 'with'. 