General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
>>Hi All,
>>
>>I currently have about 10 databases. One of the databases stores information about all the other databases. I want to write stored procedures that all dtabases will access without having to manage each procedure from within each database, since each will all be the same, respectively.
>>
>>Is it acceptable to store all my procedures in one database where all my other databases will call the procedures?
>>
>>Are there performance implications?
>>
>>Thanks in advance....
>>Michael
>
>Michael,
>While it is acceptable, I don't see how it will be beneficial to have them stored singularly except for maintenance purposes. I could be mistaken depending on what T-SQL code you have in your SPs.
>
>If you are processing row sets for individual databases and tables, I see a big performance hit in centralizing your SPs. If you centalize then you would need to use Dynamic SQL in order to process databases and tables specified by the user. The SQL statements would have to compiled and optimized whenever the SP was run thereby short circuiting one of the reasons to use SPs. In order to be pre-optimized, you would need to hard code the database and table names in the SP at design time.
>
>My $0.02.
Thanks for your response....
Actually, all the databases I speak of are identical. They are mirror images of each other. Each one supports a different web site, but each one is created equal.
That is what made me propose the question....
So I could in fact hardcode the table names, but I would not be able to hardcode the database names. So, as such I would assume I would agian lose the benefit of the SP????
Thanks again
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only