sql server mseq: Use of "USE databasename" Within a Stored Procedure -- sql server mseq

Re: Use of "USE databasename" Within a Stored Procedure

Hugo Kornelis
12/6/2005 10:55:50 PM

[quoted text, click to view]

Hi Rita,

First of all: why do you need to use the same procedure in different
databases? The only situation I can imagine if it's a procedure that
performs general DB-maintenance tasks. If you use this in a procedure
that performs business logic, than you might have a flawed design. If
you post more information, we can help you improve your design.

That being said, the answer to your question is that the only better way
than using dynamic SQL to do this is to not use it at all. Related data
should be in one database.

Are you aware of the risks of dynamic SQL? I heartily recommend you to
read Erland's site: http://www.sommarskog.se/dynamic_sql.html

Best, Hugo
--

Re: Use of "USE databasename" Within a Stored Procedure

RitaG
12/7/2005 7:39:02 AM

Hi Hugo,

Thanks for your response.

We do data loads for different companies and each company has it's own SQL
database within the same SQL server.The logic and stored procedures for each
company's data load is different so it has not been an issue before. We now
have taken on the task of 30 more data loads but they all use the same logic
and stored procedures. Rather than duplicate the logic 30 times in each of
the databases I wanted 1 common place where all the logic and stored
procedures for these 30 data loads reside and all 30 move through this common
place but load the data into their separate databases. We have a whole system
designed around these separate databases and so I cannot put everything into
just 1 database for these 30.

Rita

[quoted text, click to view]

Re: Use of "USE databasename" Within a Stored Procedure

RitaG
12/9/2005 3:56:03 PM

Thanks Hugo.
I appreciate your comments and suggestions.

I started my Stored Procedures (SP) using the logic as described above and
it's actually not as bad as I though since there is only 1 variable passed to
the SP.

I do have one more question though.
Is using "sp_executesql @SQL" where there is a "Use databasename" within
@SQL a lot slower than running the SP within a database?

Thanks,
Rita

[quoted text, click to view]

Re: Use of "USE databasename" Within a Stored Procedure

Hugo Kornelis
12/10/2005 12:34:23 AM

[quoted text, click to view]

Hi Rita,

In this case, using seperate databases might be the better option.
Companies that happen to use the same table structure and procedures now
will probably start to diverge at a later time.

I would therefor also recommend copying the stored proc in all 30
databases. It's a bit more work now, but that will pay itself back later
when some of these companies want to tweak their DB and others don't.

Best, Hugo
--

Re: Use of "USE databasename" Within a Stored Procedure

Hugo Kornelis
12/10/2005 10:48:50 PM

[quoted text, click to view]

(snip)
[quoted text, click to view]

Hi Rita,

I have no idea. I don't expect to see much speed difference, but I never
tested this.

I prefer not to use dynamic SQL if I can avoid it, for reasons more
compelling than speed. See http://www.sommarskog.se/dynamic_sql.html.

Best, Hugo
--

Re: Use of "USE databasename" Within a Stored Procedure

RitaG
12/12/2005 7:46:02 AM

Thanks Hugo. Interesting article, by the way.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server mseq group.