• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

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
0
yonbret
Asked:
yonbret
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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

0
 
Brian CroweCommented:
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

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Its working for me on sql 2012
0
 
Russell FoxDatabase DeveloperCommented:
It looks okay to me, though it might be choking because you're calling your recursive CTE "result" which is a SQL reserved word. Try just changing that to something else, like MyResult:
CREATE FUNCTION [dbo].[getChildren](@parent VARCHAR(25))
	RETURNS TABLE 
AS 
RETURN

WITH MyResult(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 MyResult

Open in new window

0
 
yonbretAuthor Commented:
Once I changed result to MyResult, it worked fine.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now