Re-use of procedures and functions, etc.


  • I have a general database design which I have inherited which has identical tables and procedures, but only with a different database names. The convention is like this:





    MyDatabase3_ etc...

    The procedures reference either the main database (MyDatabase1) or (MyDatabase1_Source) or both, but never any of the others. So there are 2 which work with each other.

    My generic question is ths: They constantly want new ones and I am using my most recent one as a template for new ones. Each time, I find a little better way to make the new ones. What I want is to be able to update the old ones with my improvements, OR, be able to use one set of procedures to access all databases, but I am not sure how to handle the object names in this single set of procedures by somehow dynamically changing the database name or something. I am not sure the best way to handle this. Would it be dynamic sql? Could I use synonyms somehow? If I did, how would I manage them?

    Right now, my procedures would have statements that look like this:

    SELECT * INTO MyDatabase1_Source.dbo.MyDestinationTable FROM MyDatabase1_Source.dbo.MySourceTable

    As it stands now, I have to change every reference to MyDatabase1 to MyDatabase2. But everything else stays consistent. Of course, as I said, when I want to change the base procedure, I want it to reflect on all of them, so ideally, I would like to be able to just change the database and table references in the procedures.

    Monday, August 05, 2013 7:45 PM


All replies