wrote:
> Hi
>
> I recoded a stored procedure into an UDF , where do i put the Maxrecusion
> option ?
>
> In my old stored procedure i did put it richt behind the last select, but in
> the UDF version i get an error.: Incorrect syntax near the keyword OPTION
> ALTER FUNCTION dbo.GetPartsInDesign
>
> (
>
> @designid int,
>
> @designquantity int=1
>
> )
>
> RETURNS TABLE
>
> AS
>
> RETURN
>
> WITH Ingredientlist (DesignID,ProdID) AS
>
> (
>
> SELECT a.DesignID,a.ProdID FROM DesignItems AS a WHERE a.DesignID=@designid
>
> UNION ALL
>
> SELECT a.DesignID, a.ProdID FROM DesignItems AS a
>
> INNER JOIN Ingredientlist AS b ON b.ProdID=a.DesignID
>
> )
>
> SELECT A.ProdID,@designquantity * COUNT(A.ProdID) as Quantity ,( SELECT
> ShortName FROM StockParts WHERE (ProdID = A.ProdID) ) as Partname FROM
> Ingredientlist AS B RIGHT OUTER JOIN
>
> Ingredientlist AS A ON A.ProdID = B.DesignID GROUP BY A.ProdID,B.ProdID
> HAVING B.ProdID IS NULL
>
> // OPTION (MAXRECURSION 100) gives me an errer

> In my old stored procedure i did put it richt behind the last select, but
> in
> the UDF version i get an error.: Incorrect syntax near the keyword OPTION
> ALTER FUNCTION dbo.GetPartsInDesign

"Sagaert Johan" <REMOVEsagaert_j@hotmail.com> wrote in message
news:%23mEP322sHHA.4796@TK2MSFTNGP05.phx.gbl...
> Hi
>
> I recoded a stored procedure into an UDF , where do i put the Maxrecusion
> option ?
>
> In my old stored procedure i did put it richt behind the last select, but
> in
> the UDF version i get an error.: Incorrect syntax near the keyword OPTION
> ALTER FUNCTION dbo.GetPartsInDesign
>
> (
>
> @designid int,
>
> @designquantity int=1
>
> )
>
> RETURNS TABLE
>
> AS
>
> RETURN
>
> WITH Ingredientlist (DesignID,ProdID) AS
>
> (
>
> SELECT a.DesignID,a.ProdID FROM DesignItems AS a WHERE
> a.DesignID=@designid
>
> UNION ALL
>
> SELECT a.DesignID, a.ProdID FROM DesignItems AS a
>
> INNER JOIN Ingredientlist AS b ON b.ProdID=a.DesignID
>
> )
>
> SELECT A.ProdID,@designquantity * COUNT(A.ProdID) as Quantity ,( SELECT
> ShortName FROM StockParts WHERE (ProdID = A.ProdID) ) as Partname FROM
> Ingredientlist AS B RIGHT OUTER JOIN
>
> Ingredientlist AS A ON A.ProdID = B.DesignID GROUP BY A.ProdID,B.ProdID
> HAVING B.ProdID IS NULL
>
> // OPTION (MAXRECURSION 100) gives me an errer
>
>
>
>
>
>

"SQL Menace" <denis.gobo@gmail.com> wrote in message
news:1182368092.844718.165660@g4g2000hsf.googlegroups.com...
> On Jun 20, 3:03 pm, "Sagaert Johan" <REMOVEsagaer...@hotmail.com>
> wrote:
> > Hi
> >
> > I recoded a stored procedure into an UDF , where do i put the
Maxrecusion
> > option ?
> >
> > In my old stored procedure i did put it richt behind the last select,
but in
> > the UDF version i get an error.: Incorrect syntax near the keyword
OPTION
> > ALTER FUNCTION dbo.GetPartsInDesign
> >
> > (
> >
> > @designid int,
> >
> > @designquantity int=1
> >
> > )
> >
> > RETURNS TABLE
> >
> > AS
> >
> > RETURN
> >
> > WITH Ingredientlist (DesignID,ProdID) AS
> >
> > (
> >
> > SELECT a.DesignID,a.ProdID FROM DesignItems AS a WHERE
a.DesignID=@designid
> >
> > UNION ALL
> >
> > SELECT a.DesignID, a.ProdID FROM DesignItems AS a
> >
> > INNER JOIN Ingredientlist AS b ON b.ProdID=a.DesignID
> >
> > )
> >
> > SELECT A.ProdID,@designquantity * COUNT(A.ProdID) as Quantity ,( SELECT
> > ShortName FROM StockParts WHERE (ProdID = A.ProdID) ) as Partname FROM
> > Ingredientlist AS B RIGHT OUTER JOIN
> >
> > Ingredientlist AS A ON A.ProdID = B.DesignID GROUP BY A.ProdID,B.ProdID
> > HAVING B.ProdID IS NULL
> >
> > // OPTION (MAXRECURSION 100) gives me an errer
>
> AFAIK MAXRECURSION is used with common table expressions (CTE's) not
> with functions
>
> Denis The SQL Menace
> http://sqlservercode.blogspot.com
> http://sqlblog.com/blogs/denis_gobo/default.aspx
>