"frankkirchner" <frankkirch***@discussions.microsoft.com> wrote in message
news:D920C018-FE81-40F2-B304-4940C3F5B1C1@microsoft.com...
>I need to use a SQL Server instance where there are about 100 databases,
>all
> with the same schema, but each relevent to a local operating company.
>
> The databases are named something like this, OPCO001, OPCO002, etc.
>
> My Web App needs to call a proc that will retrieve data using the same
> select statement, but with the context of the OPCO being differrent each
> time
> (passed in a parm).
>
> Without using Dynamic SQL, is there a way to just change the context of
> the
> database inside the stored procedure?
>
> Is there a performance hit when running a contructed select statment using
> dynamic SQL versus coding the procedure with a 100 differrent select
> statments and using just the relevent one?
>
> The DB Administrators/ Creators suggest that on the application level I
> use
> a differrent Login mapped to each Database - but then the stored proc
> would
> have to be built in each of the 100 DBs. Is this how a scenario like this
> is
> noramlly done in your real worlds?
> --
> Frank Kirchner
> Programmer Analyst
> Regional Development
> SYSCO Corp.

"Aaron Bertrand [SQL Server MVP]" wrote:

> Sometimes dynamic SQL is the best answer.
>
> However, if the app can pass in the database name, why don't you have a copy
> of the procedure in each database, and the app can connect to the relevant
> database instead.  The main concern with this is, oh my god, I have 100
> databases so now I have 100 copies of the stored procedure to manage.  This
> management is trivial to control through scripts.  You make changes to one
> database and then roll the alter script out to all the other databases.
> This is particularly easy if all of the target databases have a consistent
> naming scheme, which it seems like you have done.  The primary system I am
> responsible for (1.5 TB across 500+ identical databases) works exactly this
> way, and code change deployments are the least of my worries.
>
> A
>
>
>
>
> "frankkirchner" <frankkirch***@discussions.microsoft.com> wrote in message
> news:D920C018-FE81-40F2-B304-4940C3F5B1C1@microsoft.com...
> >I need to use a SQL Server instance where there are about 100 databases,
> >all
> > with the same schema, but each relevent to a local operating company.
> >
> > The databases are named something like this, OPCO001, OPCO002, etc.
> >
> > My Web App needs to call a proc that will retrieve data using the same
> > select statement, but with the context of the OPCO being differrent each
> > time
> > (passed in a parm).
> >
> > Without using Dynamic SQL, is there a way to just change the context of
> > the
> > database inside the stored procedure?
> >
> > Is there a performance hit when running a contructed select statment using
> > dynamic SQL versus coding the procedure with a 100 differrent select
> > statments and using just the relevent one?
> >
> > The DB Administrators/ Creators suggest that on the application level I
> > use
> > a differrent Login mapped to each Database - but then the stored proc
> > would
> > have to be built in each of the 100 DBs. Is this how a scenario like this
> > is
> > noramlly done in your real worlds?
> > --
> > Frank Kirchner
> > Programmer Analyst
> > Regional Development
> > SYSCO Corp.
>
>
>

"Aaron Bertrand [SQL Server MVP]" wrote:

> > A follow up question then, is there no way - no undocumented SP, no work
> > around, to change the context inside of a stored procedure? Why isn't
> > there
> > one?
>
> You'll have to ask Microsoft that one.  The typical workaround is to create
> a dynamic SQL string.
>
> > I mean, you are managing your 500+ contexts via the application; isn't
> > that
> > kind of going against a principle that says keep the business logic in the
> > DB
> > and put presentation only in the App?
>
> The business logic is in the DB.  The app connects to a central database,
> and says, I am interested in customer 150.  Where do I find him?  The
> database tells him back, he's in database xyz on server abc.  Now, the app
> can go create a connection to that database (or prefix its calls with the
> database name, similar to what you would do in dynamic SQL).  Note that the
> app cares about the customer or entity, not the database name specifically.
> So whether it passes the database name into a stored procedure, or in the
> connection string, does not change the location of the business logic and
> does not really change how the app behaves or where the presentation exists.
>
> If avoiding dynamic SQL is important to you, this is how I would do it.
> Especially if there's a chance that in the future you might want different
> business logic in the stored procedure for customer 1 vs. customer 2.  As
> these conditions grow, so does your central procedure.  And if you are going
> to scale out some of these databases to multiple servers, it makes more
> sense for the app to go connect to the relevant server than to continue to
> call a single procedure that may dynamically prefix the object name with
> both server and database.  Now you are using two database servers to help
> pull a query that really only should have been run on one.
>
> I can't decide for you, just like I can't likely convince you that a Porsche
> is the best car for you.  There are a lot more variables here than just "one
> is better than the other."
>
> A
>
>
>