can't create SP with a CTE

hi
i can't create a stored proc
with a CTE ( for recursion )
if i execute the query it works fine
but when i try to put it in a stored proc, it fails
with an error like
"Incorrect syntax near 'with'. If this is intended to be a common table
expression, you need to explicitly terminate the previous statement with a
semi-colon"
this is the query
--
with recursivo( _regla, _verbo, _pregla, _pverbo )
as (
select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
from VerboFlujo f
where f.vf_regla= 199 and f.vf_verbo= 0
union all
select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
from VerboFlujo f
inner join recursivo x on
x._regla= f.vf_regla and
x._verbo= f.vf_verbo
)
select *
from recursivo
--
atte,
Hernánsolved !
i have a SET NOCOUNT ON before the CTE
it should be SET NOCOUNT ON ;
( with a semicolon)
--
atte,
Hernán
"bajopalabra" <bajopalabra@.hotmail.com> escribió en el mensaje
news:Ot6FoWsnGHA.3512@.TK2MSFTNGP02.phx.gbl...
| hi
| i can't create a stored proc
| with a CTE ( for recursion )
|
| if i execute the query it works fine
| but when i try to put it in a stored proc, it fails
| with an error like
| "Incorrect syntax near 'with'. If this is intended to be a common table
| expression, you need to explicitly terminate the previous statement with a
| semi-colon"
|
| this is the query
| --
|
| with recursivo( _regla, _verbo, _pregla, _pverbo )
| as (
| select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
| from VerboFlujo f
| where f.vf_regla= 199 and f.vf_verbo= 0
| union all
| select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
| from VerboFlujo f
| inner join recursivo x on
| x._regla= f.vf_regla and
| x._verbo= f.vf_verbo
| )
| select *
| from recursivo
|
| --
| atte,
| Hernán
|
|