SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    Say WHA?! goober's Avatar
    Join Date
    Sep 2000
    Location
    United States
    Posts
    1,921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question SQL -- One stored proc for multiple DBs?

    Hey, all!

    Basically, I have a bunch of logic that would be better in a stored procedure, but we have multiple clients, each with their own copy of the code we're running on a separate physical DB. All the databases are on the same server (SQL 2000).

    My question is, is it possible to create sort of a "global stored procedure" that will execute as a stored procedure on whatever database calls it? In other words, if i have two DBs, "client1" and "client2", is there a way to have a stored procedure, "proc1" out there so that both the client1 and client2 databases could call it and have it work its magic?

    Another question that deserves to be asked: is this the way to go in such a scenario? I would like something that has the business logic in place and is re-usable to avoid so much duplicate procedure code.

    Thanks in advance for any help!

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    You could probably gin up something that will handle this scenario. That said, I would just add the proc to the script to generate each client's database. If it changes, just run the alter script against all databases. Which is reasonably easy to automate and alot cleaner than having a shared dependency.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •