Passing DB as parameter to stored procedure

I'm working with an application that has multiple databases for different
clients. I have a stored procedure that I would like to use for all of
these databases that is stored in a separate database. In the past, I have
passed the database name to the stored procedure and used dynamic sql to
build the select statements. In this case, I need to select to a temporary
table and then have a second query that uses the data in the temporary
table. With dynamic sql, the temporary table is removed after the first
query finishes, so this approach won't work.
Is there a way to switch databases in a stored procedure? In the foxpro
days, I think there was a Set Database procedure that would allow you to do
this.
TIA,
JohnSee: http://www.sommarskog.se/dynamic_sql.html
There is a section which explains getting data from another database.
Anithsql

Passing a Database name as a parameter

I'm currently testing a system that replicates data across a number of different databases. Once testing has been completed I use a stored procedure to reset the data on the master database so that additional tests can be run. I want to write a stored procedure that will reset the data on slave databases the testing has just been run on, but rather than have a stored procedure on each slave just have one on the master, as I could be testing across 1-n slaves.

I can access slave data from the master datbase with [slave1].[dbo].[target-table], and am looking at passing the slave name as a parameter to the stored procedure so the command would be [@.SlaveName].[dbo].[target-table], but any text inside the [ ] seems to be taken as literal string

Any one have any pointers?

If the count or names of your slave databases is/are likely to change then you'd have to do this using dynamic SQL. You could load a cursor with the contents of 'SELECT [name] FROM master.sys.databases WHERE <--insert criteria-->' then iterate through the cursor and create and execute a SQL string for each value that's returned.

For information only, there's an undocumented stored proc called sp_MSForEachDB that can be used to execute a SQL script in each database. However, be warned that undocumented stored procs could be dropped or significantly changed between SQL Server releases, or even service packs, so should not be used in production code.

Chris

|||

Solution I cam up was in the stored procedure to have a

EXEC ('USE '+@.SlaveName +'<action to take>')

Pass in encrypted password parameter somehow?

I have multiple databases, one for each client. I created a master report. Then I created links to this report for each client database in separate client-specific folders. I parameterized the connection string so that the server name and the database name are parameters of the linked report. The connection string is: ="data source=" & Parameters!SrvrName.Value & ";initial catalog=" & Parameters!DbName.Value.

The problem is that the security for now is Windows Authentication. (We are in the testing phase). However, my users will be both internal (company) and external (client) users. We do not want to set up database user IDs for each client user. I could create a single SQL account that has read access to all databases, relying on the Reporting Services security. But I would prefer to create separate SQL accounts for each database as the information is sensitive. Then I would probably pass that information in as a parameter to the report as well. Either way, I would have to include the password somehow. But, if I do that, I'm not sure how to include the password parameter. I would like it to be secure and passing it in as a parameter is not very secure.

I would really appreciate some suggestions on how to proceed.

Consider storing the database credentials in the Report Server web.config file instead of passing them as parameters. The ExpressionBasedConnection report in this download shows how this could be done.