View Single Post
  #2 (permalink)  
Old January 8th, 2007, 05:11 PM
planoie's Avatar
planoie planoie is offline
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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
Reply With Quote