asked on

T-SQL for Function

I am updating from SQL 2005 to SQL Server 2012 R2.

I have an old function that apparently isn't compatible with SQL Server 2012.

What do I need to do to the below code, to make it work in SQL 2012?

create function [dbo].[getChildren](@parent varchar(25))
returns table as return
with result(ItemChild) as
(select ItemChild from AlereBillMaterial where ItemParent=@parent
union all
select t.ItemChild from result r
inner join AlereBillMaterial t on t.ItemParent = r.Itemchild)
select * from result

Kyle Abrahams, PMP

Try the following:

create function [dbo].[getChildren](@parent varchar(25))
returns table as return
(
   with result(ItemChild) as  
     (select ItemChild from AlereBillMaterial where ItemParent=@parent
      union all
      select t.ItemChild from result r
      inner join AlereBillMaterial t on t.ItemParent = r.Itemchild
    )
   select * from result
)

Open in new window

Brian Crowe

Let me know if you get any syntax errors...

CREATE FUNCTION [dbo].[getChildren]
(
	@parent VARCHAR(25)
)
RETURNS @Children TABLE
(
	ItemChild	VARCHAR(25) --assuming the datatype, change if necessary
)
BEGIN

	INSERT INTO @Children (ItemChild)
	SELECT ItemChild
	FROM AlereBillMaterial
	WHERE ItemParent = @parent
	UNION ALL
	SELECT t.ItemChild
	FROM result r
	INNER JOIN AlereBillMaterial t
		on t.ItemParent = r.Itemchild

	RETURN
END

Open in new window

Aneesh

Its working for me on sql 2012

ASKER CERTIFIED SOLUTION

Russell Fox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yonbret

ASKER

Once I changed result to MyResult, it worked fine.