USE [STZTR]
GO
/****** Object:  UserDefinedFunction [dbo].[FindReportByName1]    Script Date: 03.06.2014 14:16:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FindReportByName1](@ReportName nvarchar(100), @AParam nvarchar(100))
RETURNS 
@t table(
Path1 varchar(1000) )AS  
BEGIN 
Declare @tt table 
(
ID int IDENTITY(1,1),
Name nvarchar(100),
ParentID int
)
insert into @tt 
	select FullName, ParentObjectID from STZTRSys.Common.vPages 
	where ContentPagePath LIKE '%'+@ReportName+'%' AND AParams LIKE '%'+@AParam+'%'

	DECLARE @CurrentID int, @MaxID int
	select @CurrentID=1,@MaxID=max(ID) FROM @tt
	while(@CurrentID<=@MaxID)
begin
declare @s varchar(1000), @Parent_ID int
SELECT @s=Name,@Parent_ID=ParentID FROM @tt where id=@CurrentID
set @CurrentID=@CurrentID+1
INSERT into @t select dbo.FindReportByID(@Parent_ID) +@s
end
--return dbo.FindReportByID(@Parent_ID)+@s
return
END