Interrogate stored procedure

Hi all,
Scratching my head and wondering if this is even possible, so thought
I'd ask the collective for some advice before I get started.
I've inherited a stored procedure which contains lots (over 5000 lines)
of calculations and I'd like to be able to work from it, a calculation
and the constituent parts. For example, say the sproc contains -
DECLARE @.a
DECLARE @.b
DECLARE @.c
DECLARE @.d
DECLARE @.e
select @.b=b, @.d=d, @.e=e from mynumbers where type = 1
select @.c = @.d+@.e
select @.a = @.b * @.c
Update mynumbers SET a=@.a, c=@.c where type = 1
Then I'd like to be able to write something, so that I can simply ask
how the sproc calculates a and I would get back -
a = b * c
c = d + e
Does that make sense? Any ideas what would be the easiest way to do
this?
Thanks in advance,
BradBrad,
You could try something like the below.
hth
Ion
/*
* shows assignments using SET in a given procedure.
* assumes that all such assignments are done on single lines
* fails when assignment line breaks across syscomment chunks
* ARGUMENTS
* @.procname the name of the stored procedure
* @.varname the name of the variable the assignment of which is sought
(without the '@.')
*/
create procedure ShowAssignment(@.procname sysname, @.varname
nvarchar(16)) AS
BEGIN
DECLARE @.proctext nvarchar(4000), @.pos smallint
-- collect the text of the procedure
DECLARE proctext CURSOR FAST_FORWARD FOR SELECT syscomments.text
FROM syscomments INNER JOIN sysobjects on syscomments.id =
sysobjects.id WHERE sysobjects.name = @.procname and sysobjects.type =
'p' AND sysobjects.uid = user_id()
OPEN proctext
WHILE @.PROCTEXT IS NULL OR @.@.fetch_status = 0
BEGIn
SET @.pos = 0 -- reinitialize your cursor. Not like a database
cursor. A text cursor.
FETCH FROM proctext INTO @.proctext
-- now, there's no guarantee the proc text won't break in the
middle of an assignment. But, into each life...
WHILE 0 < PATINDEX('%SET [@.]' + @.varname + '[ =]%',
substring(@.proctext, @.pos + 1, len(@.proctext) - @.pos - 1))
BEGIN
SET @.pos = @.pos + PATINDEX('%SET [@.]' + @.varname + '[ =]%',
substring(@.proctext, @.pos + 1, len(@.proctext) - @.pos))
IF 0 < charindex(char(13), @.proctext, @.pos)
PRINT SUBSTRING(@.proctext, @.pos, charindex(char(13),
@.proctext, @.pos) - @.pos)
ELSE -- if we break here, show what we've got
PRINT '(partial)' + SUBSTRING(@.proctext, @.pos,
len(@.proctext) - @.pos)
END
END
CLOSE proctext
DEALLOCATE proctext
END
GO|||Hi
Unless type contains unique values you will get potentially random results
from the update.
You could try using
Update mynumbers SET a=b * (d+e), c=(d+e) where type = 1
depending on what you want to achieve.
John
"bradsalmon" wrote:

> Hi all,
> Scratching my head and wondering if this is even possible, so thought
> I'd ask the collective for some advice before I get started.
> I've inherited a stored procedure which contains lots (over 5000 lines)
> of calculations and I'd like to be able to work from it, a calculation
> and the constituent parts. For example, say the sproc contains -
> DECLARE @.a
> DECLARE @.b
> DECLARE @.c
> DECLARE @.d
> DECLARE @.e
> select @.b=b, @.d=d, @.e=e from mynumbers where type = 1
> select @.c = @.d+@.e
> select @.a = @.b * @.c
> Update mynumbers SET a=@.a, c=@.c where type = 1
>
> Then I'd like to be able to write something, so that I can simply ask
> how the sproc calculates a and I would get back -
> a = b * c
> c = d + e
> Does that make sense? Any ideas what would be the easiest way to do
> this?
> Thanks in advance,
> Brad
>|||Only a human with the time to read through the code can do what you are
asking.
You can write code that will identify every occurance of the variable in
question, but that will only get you half the answer.
If a = b + c
you still need to know what b and c equal.
So:
b=x/y
x=y*d
Now go look up x, y, and d
Now, look up the calculations that go into x, y, and d and find the source
for each of them.
Now, substitute the original values in your formula to list out all the
pieces of the formula, along with the procedural logic which determines
which calculations to use. Not to mention the variables used in
if/then/else statements which control whether or not these values even get
set.
Congratulations, you have just recreated the original code.
The best you can do is go through the code in a good text editor with find
functions and try to figure it out. Make sure you pay particular attention
to what is going on within any nested ifs, cursors, etc. Any automated
script for this will give you the wrong formula every time.
"bradsalmon" <bradsalmon@.yahoo.com> wrote in message
news:1147183833.980625.52860@.j73g2000cwa.googlegroups.com...
> Hi all,
> Scratching my head and wondering if this is even possible, so thought
> I'd ask the collective for some advice before I get started.
> I've inherited a stored procedure which contains lots (over 5000 lines)
> of calculations and I'd like to be able to work from it, a calculation
> and the constituent parts. For example, say the sproc contains -
> DECLARE @.a
> DECLARE @.b
> DECLARE @.c
> DECLARE @.d
> DECLARE @.e
> select @.b=b, @.d=d, @.e=e from mynumbers where type = 1
> select @.c = @.d+@.e
> select @.a = @.b * @.c
> Update mynumbers SET a=@.a, c=@.c where type = 1
>
> Then I'd like to be able to write something, so that I can simply ask
> how the sproc calculates a and I would get back -
> a = b * c
> c = d + e
> Does that make sense? Any ideas what would be the easiest way to do
> this?
> Thanks in advance,
> Brad
>