Stored Procedure Reuse

[Dadou]

I wonder if there is a solution for this in SQL 2000 (or do I have to
wait for SQL 2005)?

I am currently in the middle of developing a 'Yahoo' style portal which
will be rolled out in about 20 or so countries. I have set up in SQL
Server one database per country. All the portals have the same
functionality - but show different data.

Is it possible to have a single database which holds stored
proceedures, functions and views and have the individual country
databases use these?

Note: I want to avoid using EXEC sp_executesql.

I look forward to some good news on this! Thank you in advance.

Dadou.

[Simon Hayes]

Re: Stored Procedure Reuse


"Dadou" <jensendarren@h otmail.com> wrote in message
news:1105415158 .405853.152310@ f14g2000cwb.goo glegroups.com.. .[color=blue]
>I wonder if there is a solution for this in SQL 2000 (or do I have to
> wait for SQL 2005)?
>
> I am currently in the middle of developing a 'Yahoo' style portal which
> will be rolled out in about 20 or so countries. I have set up in SQL
> Server one database per country. All the portals have the same
> functionality - but show different data.
>
> Is it possible to have a single database which holds stored
> proceedures, functions and views and have the individual country
> databases use these?
>
> Note: I want to avoid using EXEC sp_executesql.
>
> I look forward to some good news on this! Thank you in advance.
>
> Dadou.
>[/color]

Proper source control and deployment is usually the best way to go - check
out the discussion on a "master" database here:

http://www.sommarskog.se/dynamic_sql.html#Dyn_DB

One other possibility might be to use a country code as part of the key in
your tables, and then have only one database, but that might not be possible
or desirable in your situation anyway.

Simon

[David Portas]

Re: Stored Procedure Reuse

If they are on the same server then why do you want separate databases
per country? As Simon has said, you could just add the country as part
of your primary keys. If you do have to take the separate databases
route then you probably won't gain much from putting the SPs in one
central DB. It would just add to code complexity unless for example you
created separate views in your master db that pointed to the correct
database, or maybe a set of partitioned views... which again begs the
question, why separate DBs?

--
David Portas
SQL Server MVP
--