FindReportByName1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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