我们可以将SQL查询中的数据库名称作为参数传递吗？

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?

我想将数据库名称作为参数传递给存储过程。问题是,有可能吗?如果有,怎么样?

2 个解决方案

#1

You can accomplish this using sp_executesql

您可以使用sp_executesql完成此操作

DECLARE @Database   NVARCHAR(255),
        @Query      NVARCHAR(MAX)

SET @Database = 'Database'
SET @Query = N'SELECT * FROM ' + @Database + '.dbo.Table'

EXEC sp_executesql @Query

#2

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'

exec GetData'campledev'

Results

dev data

(1 row(s) affected)

(1排受影响)

exec GetData 'sampleqa'

exec GetData'sampleqa'

Results

qa data

(1 row(s) affected)

(1排受影响)

#1