Our application uses multiple databases which are structured identically. I would like to be able to build a stored procedure once and apply it to multiple databases instead of creating numerous copies of each stored procedure. I am therefore looking for away to pass the database name as a parameter instead of statically assigning it in the report code. Is this possible?
Can the Database name be a user parameter or other variable?
(2 posts) (2 voices)-
rjparth, Member
-
myDBR Team, Key Master
It is possible, but there really is no optimal solution.
A stored procedure is designed to access specified database. In order to make it access multiple databases you would need to use dynamic SQL, prepared statements. Maintaining dynamic SQL is a lot of work.
Another option is to use views which would combine data from multiple databases with extra field identifying the source database. The downside of this approach is that performance might quickly become an issue when the number of databases increases.
--
myDBR Team
Reply
You must log in to post.