Common Table Expressions

microsoft.public.sqlserver.programming

29 Jun 2006 1:43 PM

Can I use a CTE inside a user defined function? If so, could you lead me to
an example.

Thanks,
ji

29 Jun 2006 2:00 PM

Omnibuzz

one example

create function fn1()
returns table
as
return(with fact_tbl as
(select 1 as num,cast(1 as bigint) as fact
union all
select num+1,fact*(num + 1) from fact_tbl where num < 20)
select * from fact_tbl
)

--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/

29 Jun 2006 2:12 PM

Thank you....

Show quote

"Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message
news:C534E46E-7279-482A-AF2F-4694086794B7@microsoft.com...
> one example
>
> create function fn1()
> returns table
> as
> return(with fact_tbl as
> (select 1 as num,cast(1 as bigint) as fact
> union all
> select num+1,fact*(num + 1) from fact_tbl where num < 20)
> select * from fact_tbl
> )
>
> --
> -Omnibuzz (The SQL GC)
>
> http://omnibuzz-sql.blogspot.com/
>