UDF in MS SQL Server returning a query starting with "with" cause 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'.
you are mixing procedure and function

for UDF, your syntax should be
CREATE or alter function x (@__employee_id uniqueidentifier)
returns table
AS
RETURN (
with t as (select 'a' as attr)
select * from t
);