Can we pass the database name in the SQL query as a parameter?

Question

Consider the following queries in which only the database name is different (on the same server)

Select * from sampledev.dbo.Sample Select * from sampleqa.dbo.Sample 

The above requests are part of the procedure. Every time I have to start a procedure, I have to make sure that it refers to the correct database (and rename it if it is not).

I want to pass the database name as a parameter to a stored procedure. The question is, is this possible? If so, how?

Answer 1

You can accomplish this using sp_executesql

 DECLARE @Database NVARCHAR(255), @Query NVARCHAR(MAX) SET @Database = 'Database' SET @Query = N'SELECT * FROM ' + @Database + '.dbo.Table' EXEC sp_executesql @Query 

Answer 2

Something simple :?

 CREATE PROC GetData ( @DatabaseName VARCHAR(255) ) AS BEGIN IF @DatabaseName = 'sampledev' SELECT * FROM sampledev.dbo.Sample ELSE IF @DatabaseName = 'sampleqa' SELECT * FROM sampleqa.dbo.Sample END 

Using:

exec GetData 'sampledev'

results

---

dev data

(1 row (s) affected)

exec GetData 'sampleqa'

results

---

qa data

(1 row (s) affected)