我正在尝试创建一个查询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> ;.分辨率运算符允许您在不使用use语句的情况下查询不同数据库中的对象. 有一些非常非常罕见的情况,可能希望允许sproc使用任意数据库.在我看来,唯一可接受的用途是代码生成器,或某种数据库分析工具,它不能提前知道所需的信息. 更新事实证明,您不能在存储过程中使用,将动态sql作为唯一明显的方法.不过,我会考虑使用
select top 100 * from db_name.dbo.table_name
而不是使用.
