dynamic object(database/schema/obj) in stored procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » dynamic object(database/schema/obj) in stored...

dynamic object(database/schema/obj) in stored procedure

Rate Topic

Display Mode

Topic Options

Author

Message

bbaley

Posted Wednesday, November 12, 2008 11:09 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 1:38 PM
Points: 14, Visits: 116

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

Post #601564

Lynn Pettis

Posted Wednesday, November 12, 2008 11:48 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 21,588, Visits: 27,385

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.

Cool

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

ALI-408105

Posted Wednesday, November 12, 2008 2:02 PM

SSC 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.

http://msdn.microsoft.com/en-us/library/ms175010.aspx

Post #601702

« Prev Topic | Next Topic »

Permissions