SP_EXECUTESQL在from子句中将数据库作为变量

Question

是否可以将参数传递到sp_executesql以确定查询的来源?这里的目标是接收作为参数的db名称和以占位符作为参数的sql文本，并将它们组合在一起。我可能可以使用REPLACE，但这样我就不会得到参数安全检查。

代码:

DECLARE @my_db VARCHAR(30) = 'MyDatabase'
DECLARE @sql NVARCHAR(MAX) = N'select top 10 * from @db.dbo.table1'

exec sys.sp_executesql @stmt = @sql, @params = N'@db varchar(120)', @db = @my_db;

结果:

'.'附近语法错误。

我正在使用SQL Server 2016

Answer 1

你可以在这里参数化数据库名称。相反，将进程名定义为一个变量:

DECLARE @DBName sysname = N'MyDatabase';
DECLARE @Proc nvarchar(500) = QUOTENAME(@DBName) + N'.sys.sp_executesql';
DECLARE @SQL nvarchar(MAX) = N'SELECT TOP 10 * FROM dbo.table1 ORDER BY SomeColumn;'; --You need an ORDER BY for reliable results

EXEC @Proc @SQL;

另一种方法是将数据库名称注入SQL:

DECLARE @DBName sysname = N'MyDatabase';
DECLARE @SQL nvarchar(MAX) = N'SELECT * FROM ' + QUOTENAME(@DBName) + N'.dbo.table1 ORDER BY SomeColumn;';
EXEC sys.sp_executesql @SQL;

对于像您这样简单的东西，我实际上建议使用前者，因为没有在语句中注入，因此您避免了SQL注入途径。如果用户输入了一个无效的数据库名，您将得到以下错误:

Msg 911, Level 16, State 4, Line 6
数据库'abc'不存在。请确保名称输入正确。