David Potahisnsky (david.potashinsky@kabbalah.com) writes:
[quoted text, click to view]

Put the stored procedure with the main logic in all databases. In the
master you can say:

DECLARE @logic_sp sysname
SELECT @logic_sp = @db + '.dbo.your_sp'
EXEC @err = @logic_sp @par1, @par2, ....

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Myrth (m0sh3_NO@SPAM_hotmail.com) writes:
[quoted text, click to view]

Actually there is: this is the way the system stored procedure works.
However, while you can add your own system stored procedures, this is
deprecated and unsupported, and also has security issues.

And anyway the scheme breaks down the day the databases are spread over
more than one server. In this case, you have no choice than to deploy
your procedure on at least every server.

So the real answer to your problem is to devise a distribution
mechanism, so that you easily can distribute updates to your code to
your databases, no matter they are all on the same server, or spread
out on several customer sites. Obviously for this you need a master
to deliver from, but this master database is not an SQL database -
it's a version control system (which may use SQL Server as its storage
engine, but that's another story).

Such a distribution can be assembled in different ways. The heart of the
procedure is always that in your version-control system you set a
baseline. The terminology changes between version-control systems,
but often you talk about "labels". Then you assemble the changes between
two labels. This can be performed by manually building a BAT-fil which
uses OSQL to install the stored procedures, or it can be more elaborate.

There are several third-party tools on the market. Popular is SQL Compare
from Red Gate, www.red-gate.com, although it works from a different
perspective than I've outlined here. Myself, I have made the tool we
use in our shop available as freeware, see http://www.abaris.se/abaperls/.

Thus, what you have is a configuration-management problem, not one of
writing stored procedures. It may seem inflexible that stored procedures
are tied to one certain database, but there are some good reasons for
this. The most important is that although the logic in the stored
procedure may be the same in two databases, the query plan that SQL Server
should use to retrieve the data may not, because the data is different
in the two databases.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

we use views successfully here. write your stored procedures to access views
instead of base tables. upon deployment you can literally generate the cross
database views from a table in your main db (table with metadata). an sp can
produce multiple of these:

IF OBJECT_ID('v_<foreign_db>_<base_table_name>') IS NOT NULL DROP VIEW
v_<foreign_db>_<base_table_name>
GO
CREATE VIEW v_<foreign_db>_<base_table_name>
AS
SELECT *
FROM <foreign_db>.dbo.<base_table_name>
GO

where <foreign_db> is a logical name of a db in your system e.g. "rep" for
reporting (not the actual deployment specific sql db name) and can be passed
as a paramter to the sp or even better -- can be persisted in a setup table.

of course using * for column_list is not "production" enough so you might
want to store view's column lists in you "metadata cross-db view definition"
table as well.

we use multiple prefixes for views names like this:
v_<host_db>_<foreign_db>_<foreign_view_name> to allow different column list
for each <host_db> that is each sub-system of the solution. so we end up
with several views pointing to a single base table (but from different
sub-systems).

cheers,
</wqw>

[quoted text, click to view]