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

This is a very comprehensive list of ways to share data between stored procedures.

Up vote 1 down vote favorite share g+ share fb share tw.

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.

) sql-server tsql stored-procedures etl link|improve this question asked Feb 12 '10 at 19:52DetectiveEric756.

This is a very comprehensive list of ways to share data between stored procedures: sommarskog.se/share_data.html.

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: 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. msdn.microsoft.com/en-us/library/ms14102....

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.