Consider the following queries, where only database name differs (on same server)
Select * from sampledev.dbo.Sample
Select * from sampleqa.dbo.Sample
The above queries are part of a procedure. Every time I have to run the procedure, I have to make sure it references the correct database (and do rename, if it is not).
I want to pass the database name as a parameter to the stored procedure. The question is, is it possible? If yes, how?
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
Something as simple as: ?
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
Use:
exec GetData 'sampledev'
Results
dev data
(1 row(s) affected)
exec GetData 'sampleqa'
Results
qa data
(1 row(s) affected)
Just like the leading answer but without SQL injection vulnerability.
First you must query the sys.databases in order to be sure to get the real Database name while not counting on the users text:
SELECT @Database = [name]
FROM sys.databases
WHERE [name] = @Database;
Now perform the query using sp_executesql:
DECLARE @Query nvarchar(200);
SET @Query = N'SELECT * FROM ' + @DBName + '.dbo.sample';
EXEC sp_executesql @Query
Full Stored procedure:
CREATE PROCEDURE [MyScheme].[MyStoredProcedure]
(
@DBName sysname
)
AS
BEGIN
SET NOCOUNT ON;
SELECT @DBName = [name]
FROM sys.databases
WHERE [name] = @DBName;
DECLARE @Query nvarchar(200);
SET @Query = N'SELECT * FROM ' + @DBName + '.dbo.sample';
EXEC sp_executesql @Query
END
GO
Or even a simpler version using QUOTENAME(...) to prevent SQL injection.
CREATE PROCEDURE [MyScheme].[MyStoredProcedure]
(
@DBName sysname
)
AS
BEGIN
DECLARE @Query nvarchar(max);
SET @Query = N'SELECT * FROM ' + QUOTENAME(@DBName) + '.dbo.sample';
EXEC sp_executesql @Query
END
GO
