SQL Server：如何在存储过程中获取数据库名称作为参数

我正在尝试创建一个查询sys.tables表的简单存储过程.

CREATE PROCEDURE dbo.test
    @dbname NVARCHAR(255),@col NVARCHAR(255)
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON

    USE @dbname

    SELECT TOP 100 *
    FROM sys.tables 
    WHERE name = @col
GO

这似乎不起作用,因为我应该在使用@dbname之后放GO,但这会终止创建这个过程？如何将此数据库选择放入此过程,以便用户可以将数据库名称作为此proc的参数？

解决方法

至少有两种方法可以做到这一点：

>使用case / switch语句(或者,在我的示例中,一个naive if..else块)来比较数据库列表中的参数,并根据该语句执行using语句.这具有限制proc可以访问已知集合的数据库的优点,而不是允许访问用户帐户拥有权限的任何内容和所有内容.

declare @dbname nvarchar(255);    
set @dbname = 'db1';    
if @dbname = 'db1'
 use db1;
else if @dbname = 'db2'
 use db2;

>动态sql.我讨厌动态sql.这是一个巨大的安全漏洞,几乎没有必要. (从这个角度看：在17年的专业发展中,我从来没有必须部署一个使用动态sql的生产系统).如果你决定去这个路由,将动态调用/创建的代码限制在一个using语句中,并且调用另一个存储的proc做实际的工作.由于范围规则,您不能自己动态地执行using语句.

declare @sql nvarchar(255);
set @sql = 'using '+@dbname+'; exec mydatabase..do_work_proc;';

当然,在你的例子中,你可以做

set @sql='select * from '+@dbname+'.sys.tables';

< schema_name&gt ;.分辨率运算符允许您在不使用use语句的情况下查询不同数据库中的对象. 有一些非常非常罕见的情况,可能希望允许sproc使用任意数据库.在我看来,唯一可接受的用途是代码生成器,或某种数据库分析工具,它不能提前知道所需的信息. 更新事实证明,您不能在存储过程中使用,将动态sql作为唯一明显的方法.不过,我会考虑使用

select top 100 * from db_name.dbo.table_name

而不是使用.

SQL Server：如何在存储过程中获取数据库名称作为参数

解决方法

猜你在找的MsSQL相关文章