Question

Passing a database name as a stored procedure parameter

Stored procedures, T-SQL

I would like to be able to pass the name of a database as a parameter to a stored procedure. This db would be used for certain queries and may be different from the db where the stored procedure runs. Is it possible to specify a database name from within the T-SQL of the stored procedure? If so, how?

Yes you can do this, however it is not recommended. You have to use a technique called dynamic SQL. There are security risks on using dynamic SQL as the user who runs the procedure much have access to the table or tables listed in the dynamic SQL, and dynamic SQL is subject to a SQL injection attack.

That said, the syntax would be something like this.

CREATE PROCEDURE MyProcedure
     @DBName sysname
 AS
 DECLARE @cmd nvarchar(2000)
 SET @cmd = N'SELECT * FROM ' + @DBName + '.dbo.SomeTable'
 exec (@cmd)
 GO

You can add more input parameters as needed.