Re-usable SQL Server stored procedures; nesting; global variables

Question

I want to make some re-useable, somewhat-dynamic TSQL code that can be called within many other stored procs, but I'm struggling with how to implement this with SQL Server.

The environment is that many distributed source systems databases which will have their own wrapper stored procedure which will call a few of these modular stored procs from an common ETLManagement DB. The wrapper sproc will call a few of these commom/re-usable sprocs (to perform operations on some dynamically defined control tables), then an INSERT INTO statment(owned and defined by the source system), and then a couple more commom/re-usable sprocs.

One main roadblock is that I can't seem to declare variables in that nested sproc, how do I declare global variables that all the source system sprocs can read from?

Am I even going about this the best way with common sproc, or is there a better way?

(Forgive my naivete of TSQL programming, I've been doing ETL with other tools till now.)

Answer 1

I would put the concept of global variables into a lookup table. It's the only equiv you have.

In addition, you may consider looking more User Defined Functions as they can be used in so many more places than a SPROC. This article is a good reference to the pros/cons:

http://www.informit.com/articles/article.aspx?p=31724

Frankly, if you're interested in having true Global Variables you might want to consider moving up to SQL Server Integration Services packages. It may be more your cup of tea given your requirements.

http://msdn.microsoft.com/en-us/library/ms141026.aspx

Answer 2

this is a very comprehensive list of ways to share data between stored procedures:

http://www.sommarskog.se/share_data.html