|
Subject:
|
Calling on a database within a stored procedure
|
|
Posted By:
|
Hadware
|
Post Date:
|
1/8/2007 12:33:42 PM
|
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
|
|
Reply By:
|
planoie
|
Reply Date:
|
1/8/2007 4:11:53 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
|
