Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

dynamic object(database/schema/obj) in stored procedure Expand / Collapse
Posted Wednesday, November 12, 2008 11:09 AM


Group: General Forum Members
Last Login: Friday, December 4, 2015 1:18 PM
Points: 14, Visits: 132
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.)

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 !!!!!

Post #601564
Posted Wednesday, November 12, 2008 11:48 AM



Group: General Forum Members
Last Login: 2 days ago @ 4:50 PM
Points: 23,515, Visits: 37,731
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.

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #601603
Posted Wednesday, November 12, 2008 2:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 23, 2012 11:52 AM
Points: 27, Visits: 50
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.
Post #601702
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse