Prepare a stored procedure work flow diagram
-
Dear Experts,
I would like to prepare a diagram which will explain the work flow of a stored procedure.
what is the basic steps that i have to follow? How to draw the diagram ?
Say for example,I would like to draw a diagram for the belowo one. How?
Create procedure P1
(
@Flag char(1)
)
as
begin
Create table #P1
(
Eno int,
Ename varchar(25)
)
if @Flag = 'Y'
Begin
Insert into #P1
Select Eno,Name from Emp
End
if @Flag = 'N'
Begin
Insert into #P1
Select Eno,Name from Emp_History
End
if @Flag = 'Z'
Begin
Insert into #P1
Select Eno,Name from Old_Employees
End
if @Flag = 'R'
Begin
Insert into #P1
Select Eno,Name from Retired_Employees
End
if @Flag = 'A'
Begin
Insert into #P1
Select Eno,Name from Adult_Employees
End
.
.
.
.
.
.
.
End -- End of Procedure
But the thing is i have to analyze a procedure and draw a work flow diagram which has more than 8000 lines.
Inputs are highly appreciable!
karthik
-
Any Inputs ?
karthik
-
This is an excellent question. I would also like to know the answer to the same.
You can try posting it in the SQL Server 2005 forum as well. My guess is there must be some SQL Server specific tools that might be doing this.
It is definitely an excellent capability to have for any SQL developer.
-
[font="Arial"]Hello,
If you have a copy of Visio, there is a object template that has data flow diagram symbols.
If I had to analyze a 8000 line sproc, I would first take and write a pesudo code description of the key steps. Preferrably on a hard copy linear printout of the sproc. Then referring to the sproc and pesudo code, I'd make a high level Visio symbol data flow of the major steps of the sproc. Then make smaller more detailed data flow diagrams of the major steps.
I'm not sure why you are doing this project but ....
Documentation can be taken too far but in IMHO the basics are:
Usually a tech spec and a high level data flow diagram is all that is required from an external sproc point of view. With in the sproc, there should be embedded comments at each step of the sproc that matches the major steps referred to in the data flow diagram. Also a Modification History comment for any significant sproc changes made over the life of the sproc should be kept at the top of the sproc source code. Date and initials to each change.
I hope this helps.
Regards,
Terry
[/font]
-
I tend to agree with Terry, but unless the sproc is overly complicated or spanning more than about 50 lines, I wouldn't necessarily document it with Visio. Good comments inside would be more helpful.
If it's hundreds of lines, I'd go with the flowchart approach in Visio (or data flow), and be sure that you deliniate references (as in notes to refer back to something ) from flows. Arrows for the latter, perhaps some highlighted link or something for the former. Number each step perhaps and reference step xx in a note.
-
Sounds good. Also, I just came across this automatic flow charting tool called Visustin.
"http://www.aivosto.com/visustin.html"
Has anyone ever used this? If it works, it can really take the pain out of drawing the diagrams.
-
"http://www.aivosto.com/visustin.html"
it is very useful.
Thank you for all.
karthik
-
I usually do these things in Visio... it works quite nicely, very smooth, very clean. HOWEVER, this morning I used the Visustin app demo version this morning and found that it does a very nice job with the process visualization. I removed most of the comments, edited some of the shapes briefly and rearranged them, slightly, then put the finished drawing in Visio for reference documentation. Very nice! I've added sample images from the app
. -
To me, regurgitating the actual code as a diagram is very nearly useless, especially in SQL Server. And, to me, creating any diagram without documenting in the code itself is also useless because it's not likely that someone will maintain the external code, that you'll be able to find the diagrams when you need them, or that people will even take the time to do so.
Anyone can study code and figure out WHAT the code is doing but it's not usually obvious WHY the code is doing what it's doing. My rule of thumb is that you should commented well enough so that if you were to remove all of the code, you should be able to easily draw a functional flowchart from the comments that remain and actually rewrite the code from those.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
.Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply