SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
bbaley
bbaley
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 132
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 !!!!!
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64383 Visits: 38779
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)
ALI-408105
ALI-408105
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search