ritag

Hi.

I have a common stored procedure (SP) that has a variable containing a
database name passed to it. Within the SP I have the statement "Use
@databasename" but get an error when I run the SP. I then defined a variable
that holds all the SQL statements (including the “USE @databasename�) and
then in the SP used ‘Exec sp_executesql !@SQL�.

This works fine but I now have to create another much more complicated SP
and I find it tricky to work this way especially when I'm passing several
variables to the SP.

Is there another way I can use the "Use" statment within a SP without having
to bundle all the SP code into a variable?

Any suggestions will be greatly appreciated :-).
Rita

hugo kornelis

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

(Remove _NO_ and _SPAM_ to get my e-mail address)

ritag

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

hugo kornelis

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

(Remove _NO_ and _SPAM_ to get my e-mail address)

ritag

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

hugo kornelis

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

(Remove _NO_ and _SPAM_ to get my e-mail address)

ritag

Thanks Hugo. Interesting article, by the way.