Before reading this article I recommend you to read my last article Finding
Real Dependencies. If I had dependencies between stored procedures I can use them to create
hierarchical view. I'm going to create five stored procedures in Northwind database:
use Northwind
go
create procedure proc111
as -- last level procedure --
go
create procedure proc112
as -- last level procedure --
go
create procedure proc211
as -- last level procedure --
go
create procedure proc11
as exec proc111 exec proc112
go
create procedure proc21
as exec proc211
go
create procedure proc1
as exec proc11
go
create procedure proc2
as exec proc21
I will use two procedures and one user defined function:
- procRoute - Finding Routes between two stored procedures
- procLoadSpHierarchy - Creating hierarchical report
- funProcWithoutComments - described in article
Finding
Real Dependencies
The first procedure is modification of procedure Route documented in MSDN Expanding
Networks article.
CREATE PROCEDURE procRoute
(@current char(40), @dest char(40), @maxlevel int = 5) AS
SET NOCOUNT ON
DECLARE @level int
CREATE TABLE #stack (proced char(40), level int)
CREATE TABLE #list (proced char(40), level int)
INSERT #stack VALUES (@current, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (select * from #stack where level = @level)
BEGIN
select @current = proced from #stack where level = @level
delete from #stack where level = @level and proced = @current
delete from #list where level >= @level
if exists (select * from #list where proced = @current)
continue
insert #list values(@current, @level)
if(@current = @dest)
begin
select replicate('--', level-1) + proced AS Hierarhija
from #list
continue
end
insert #stack
select DependOnProc, @level + 1 from #TempDepend where ProcName = @current and @level < @maxlevel
if @@rowcount > 0
select @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE
GO
CREATE PROCEDURE procLoadSpHierarchy AS
set nocount on
------------------------------
CREATE TABLE [#TempDepend] (
[ProcName] [varchar] (40) NULL ,
[DependOnProc] [varchar] (40) NULL
) ON [PRIMARY]
------------------------------
SELECT so1.id as ID,
so1.name As ProcName,
dbo.funProcWithoutComments(sc.text) as ProcText
into #T1
FROM sysobjects so1
INNER JOIN syscomments sc
on so1.id = sc.id
WHERE
so1.type = 'P' and so1.name not like 'dt_%'
-------------------------------------------------------------------
INSERT INTO #TempDepend
SELECT left(#T1.ProcName,30), left(T2.DependOnProc,30) from
#T1
INNER JOIN
(select id, name as DependOnProc from sysobjects where type = 'P' and name not like 'dt_%') T2
on #T1.ID <> T2.ID
WHERE #T1.ProcText LIKE '%' + Replace(T2.DependOnProc,'_','[_]') + '[' + char(9)+ char(10)+ char(13)+ char(32) + char(59) + ']%'
AND
CHARINDEX(#T1.ProcName, #T1.ProcText)
<>
CHARINDEX(T2.DependOnProc, #T1.ProcText,CHARINDEX(#T1.ProcName, #T1.ProcText)+1)
ORDER BY 1,2
------------------------------------------------------------------
CREATE TABLE #Temp (
[br] [int] IDENTITY (1, 1) NOT NULL ,
[ImeProc] [varchar] (40) NULL
) ON [PRIMARY]
declare @Pr1 varchar(40)
declare @Pr2 varchar(40)
declare @OldPr1 varchar(40)
declare Curs cursor for
select T1.ProcName, T2.ProcName from
(select ProcName from #TempDepend
where Procname not in (select DependOnProc from #TempDepend) group by ProcName) T1
inner join
(select DependOnProc as ProcName from #TempDepend
where DependOnProc not in (select ProcName from #TempDepend) group by DependOnProc) T2
on T1.ProcName <> T2.ProcName
open Curs
fetch next from Curs into @Pr1, @Pr2
while @@FETCH_STATUS = 0
begin
if @OldPr1 <> @Pr1 insert into #Temp values ('------------------------------')
insert into #Temp exec procRoute @Pr1, @Pr2
set @OldPr1 = @Pr1
fetch next from Curs into @Pr1, @Pr2
end
close Curs
deallocate Curs
select '>' + ImeProc from #Temp
drop table #Temp
drop table #TempDepend
drop table #T1
GO
Running procedure procLoadSpHierarchy in Northwind database will yield:
--------------------------------
>proc1
>--proc11
>----proc111
>proc1
>--proc11
>----proc112
>------------------------------
>proc2
>--proc21
>----proc211
>------------------------------
>procLoadSpHierarchy
>--procRoute
-------------------------------
I think this kind of report can be very useful for documentation and specially
when we are studying or supporting some complex database developed by someone
else. In this way we can easy understand business logic flow in database. In addition, controlling nested transactions is more simple when we have this
report. Finally, it is easier to specify error handling using this report and roll
back all modifications to the first level.
