[thelist] ADOX question

Chris Blessing webguy at mail.rit.edu
Fri Oct 11 10:14:01 CDT 2002


Dave-

Use "sp_helptext <your_stored_proc>" to see all the various versions and
code associated with a particular sp.  Example scenario:

You have add_event;1
You want to know if there are more versions of that sp
"sp_helptext add_event" will tell you

If you want to define more versions of a stored proc, just create them with
the version number appended to the name:

create procedure my_proc;1 as
	...

go

create procedure my_proc;2 as
	...

etc.

What this does is essentially just group the stored procs together by name.
You can call each individually using the ;[n] notation.  If you need to drop
the "my_proc" sp above, "drop procedure my_proc" will remove both versions,
since they're contained in the same group.

Can you tell me where this version info is showing up?  Are you using
sp_helptext to display the procs right now?  If you look through EM at the
database itself, do you see version numbers on the names of the procedures?

Chris Blessing
webguy at mail.rit.edu
http://www.330i.net

> >add_event;1 is the 1st version of the add_event sp.  You could
> also have an
> >add_event;2 and ;3 and so-on.
>
> Interesting, I didn't know it did that. How do I update the
> version number?
> I've modified most of them over time, but they all still report
> version 1...
>
> >How did you create these stored procs?
>
> Manually using Query Analyzer, e.g. "create proc foobar @yadda yadda
> yadda..."
>
> There's about 250 of them.
>
> I'm trying to gather metrics on them and find similarities, what is and
> isn't being used, etc so I can trim the database. I figure I can probably
> lose 25% of the procedures due to redundancy, testing/experimenting, or
> because they are orphaned and no longer used.
>
> Thanks for your help, it answered one of my questions! :)
> -dave




More information about the thelist mailing list