<PRE>yes you can use CTE where you want even in functions
<PRE>CREATE FUNCTION dbo.fn_EmpYearCnt(@EmpID AS INT) RETURNS TABLEASRETURN WITH EmpYearCnt AS ( SELECT YEAR(OrderDate) AS OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts FROM dbo.Orders WHERE EmployeeID = @EmpID GROUP BY YEAR(OrderDate) ) SELECT * FROM EmpYearCnt;GO</PRE>
</PRE>
Main Topics
Browse All Topics





by: chapmandewPosted on 2009-10-12 at 07:17:25ID: 25551416
1. yes
2. kinda-you need to put a ; before the CTE
create procedure testproc
as
begin
;with cte
as
(
select 'hello'
)
select * from cte
end