p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Language (http://p2p.wrox.com/forumdisplay.php?f=100)
-   -   Calling on a database within a stored procedure (http://p2p.wrox.com/showthread.php?t=52409)

Hadware January 8th, 2007 01:33 PM

Calling on a database within a stored procedure
 
Hi,
I am working with multiple databases on a server.
What I am attempting is to get data from two databases on one stored procedure.
Here is an example of what I'm trying to do:
create procedure sp_procedure
(
@variable int
)
declare @anothervariable char(3)
select @anothervariable = afield from atable where something = @variable
use dbo.@anothervariable
select anotherfield from anothertable where somethingelse = @variable

The afield column contains the database name.
What method within the stored procedure can I use to get to the other database?

Thanks,
Eric


planoie January 8th, 2007 05:11 PM

Usually, if you need to programmatically change a database/table/field within a query, you'd need to employ some sql command construction. Basically, the same way as you'd dynamically construct a query in program code, you need to construct the query within the stored procedure, then execute it. Use can use the EXEC(string) SQL function to execute constructed SQL.

Keep in mind, however, that dynamic SQL like this is inefficient and kind of defeats the pre-compiled sql benefit you get by having a stored procedure. Fortunately, you may only need to run the USE command dynamically if you only have to switch databases (and they all have the same schema).

-Peter


All times are GMT -4. The time now is 01:05 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.