Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Viewing the Hierarchy of Stored Procedures

By Stefan Popovski,

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.

Total article views: 6289 | Views in the last 30 days: 3
 
Related Articles
FORUM

create Procedure within Procedure

create Procedure within Procedure

FORUM

Creating Stored Procedure with SELECT ... inside

how to create a SP with select inside

ARTICLE

Stairway to Database Design Level 6: Procedure Bodies

Having covered the procedure headers in SQL Server in the previous level, Joe tackles the subject of...

FORUM

Creating Select Query with User Supplied Value?

Need to create a select query where I can supply a range of data or value for retrieval.

FORUM

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

In Stored procedure getting Error nesting level exceeded (limit 32)

Tags
miscellaneous    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones