存储过程中的不同数据库[重复]

This question already has an answer here:

这个问题已经有了答案:

• Set database name dynamically in SQL Server stored procedure? 3 answers
• 在SQL Server存储过程中动态设置数据库名?3答案

I have following situation:

我有以下情况:

Input parameter in stored procedure is database name. So, depends on this parameter query is executed on different database. Every query is same, below is only simple example.

存储过程中的输入参数是数据库名。因此，取决于这个参数查询在不同的数据库上执行。每个查询都是一样的，下面只是一个简单的例子。

How to write stored procedure (except using dynamic sql) to avoid IF...ELSE statement like in code bellow.

如何编写存储过程(除了使用动态sql)以避免…ELSE语句，如下面的代码。

ALTER PROCEDURE [dbo].[usp_Item_GetAll]
(
    @DBName nvarchar(255) = ''  
)
AS
BEGIN

IF @DBName = 'ItemUsers'
BEGIN
    SELECT
    *
    FROM ItemUsers.dbo.vW_DAM_ItemWithAttribute
END

ELSE IF @DBName = 'CollectionUsers'
BEGIN
    SELECT
        *
    FROM CollectionUsers.dbo.vW_DAM_ItemWithAttribute
END

1 个解决方案

#1

You may create a string that contains the dynamic query along with database name and then execute that query using EXEC command something like this

您可以创建一个包含动态查询和数据库名称的字符串，然后使用EXEC命令执行该查询。

ALTER PROCEDURE [dbo].[usp_Item_GetAll]
(
    @DBName nvarchar(255) = ''  
)
AS
BEGIN

DECLARE @SQL varchar(MAX)
SET @SQL='SELECT * FROM ['+@DBName+'].dbo.[vW_DAM_ItemWithAttribute]'
EXEC(@SQL)
END