Creating sproc stubs for backwards compatability

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating sproc stubs for backwards compatability

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-11 : 08:35:55

Adam writes "Hi,

I'm working with a complex .net app with a sql back end. I'm trying to enforce a naming convention for sprocs and udfs. For compatability reasons the old sprocs will still have to exist until all the code that refers to them is pointed to the new name. I don't want to have to maintain 2 versions of every sproc.

My high level thought was to use the "rename method" refactoring technique:
1) rename the sproc to the proper naming convention using sp_rename.
2) create a stub using the original sprocs name which would just call the renamed sproc.
3) over time change sproc references in the code base to point to the properly named sproc
4) remove the old sproc

This way all existing sql and .net code should continue to work because the original sproc name still exists (albeit just a stub that passes execution to another sproc).
At a later stage I can then search through all the code and gradually replace calls to the old sprocs to the proper named sprocs. Then I can start removing the old sprocs.

I've got most of the way to solving point 1) and 2) by writing a program (with the aid of SQL-DMO) to:
1) list all the user sprocs
2) rename them to the proper convention (sp_rename)
3) get the parameter and permissions list for each sproc
4) create a stub sproc with the same parameters and permissions that just executes the renamed sproc

All is well until one of the sproc parameters is specified with a default value.

Original sproc
create proc SomeProc
@tablename varchar(50) null -- Note default value
as
. . . .
. . . .
go

is renamed (via sp_rename) to
create proc stdSomeProc -- Note new name
@tablename varchar(50) null
as
. . . .
. . . .
go

my app then creates the stub:
create proc SomeProc
@tablename varchar(50) -- note no default value
exec stdSomeProc @tablename

The upshot of this is that I can call stdSomeProc without any params but I can no longer call SomeProc the same way because it quite rightly says that I'm missing a parameter. So no more backwards compatability...

I just can't seem to find an object/sproc/query that exposes the default value for a sproc parameter so that I can put it in the stub.

Maybe I just haven't looked in the right doco or maybe my whole approach is wrong. I'd be most interested in someone's (constructive) thoughts about this!

cheers,

Adam."

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-01-11 : 13:01:01

The definition of the stored procedure can be found in the syscomments table (provided that it has not been encrypted). You could parse through the string to find the default values.

HTH

=================================================================

Scriptures, n. The sacred books of our holy religion, as distinguished from the false and profane writings on which all other faiths are based.
-Ambrose Bierce, writer (1842-1914) [The Devil's Dictionary]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources