November 12, 2008 at 11:09 am
Hello,
I am trying to figure out how to reference multiple database/objects in stored procedures.
I know how to create dynamic SQL and exec() etc. but I don't think that will help here as I am dealing with cursors etc. (although I could do without the cursors with MUCH rewriting).
Essentially, a given server will have multiple databases that are identical schemas, different data, and, a utilities schema/db where the generic objects live.
i.e. (greatly simplified and renamed, etc.)
CompanyDB1
CompanyDB2
CompanyDB3
CompanyDB4
Utilities
I would like to have a single stored procedure in [Utilities] that takes a parameter (or more likely reads from a table) for which Company Db will be referenced....
The sp pulls and relates data from Utilities to CompanyX data, and does a bunch of stuff with it, and populates [Utilities] tables....
so right now, I have a (nearly) identical sp in each db....
I would like to have one to maintain/test in [Utilities] and do;
exec [utilities]..[MyProc] 'CompanyDb1', 'a value', 'anotherparamvalue'....
exec [utilities]..[MyProc] 'CompanyDb2', 'a value', 'anotherparamvalue'....
exec [utilities]..[MyProc] 'CompanyDb3', 'a value', 'anotherparamvalue'....
How should I do this ?
Thanks !!!!!
November 12, 2008 at 11:48 am
If you have to have one stored proc in the Utilities database, then you will need to go with dynamic sql. I'd also look at writing your procedures so that they don't use cursors unless absolutely necessary.
I'd also look at how you write your dynamic sql. If the differences are actually in the where clauses and the values passed to it, I'd look at use sp_executesql instead of execute(). sp_executesql can use cached plans where as execute() can't.
November 12, 2008 at 2:02 pm
Does this help???
table variables provide the following benefits:
A table variable behaves like a local variable. It has a well-defined scope. This is the function, stored procedure, or batch that it is declared in.
Within its scope, a table variable can be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table cannot be used in the following statement:
SELECT select_list INTO table_variable
table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they are defined.
CHECK constraints, DEFAULT values and computed columns in the table type declaration cannot call user-defined functions.
table variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used.
Transactions involving table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources.
http://msdn.microsoft.com/en-us/library/ms175010.aspx%5B/url%5D
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply