SQL Server：如何在存储过程中获取数据库名称作为参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3943823/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: how to get a database name as a parameter in a stored procedure
提问by jjoras
I'm trying to create a simple stored procedure which queries a sys.tables table.
我正在尝试创建一个查询 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
This does not seem to work cause I should put GO after USE @dbname but this terminates the creation of this procedure? How can I put this database selction into this procedure so that a user can give a database name as a parameter for this proc?
这似乎不起作用，因为我应该在 USE @dbname 之后放置 GO 但这会终止此过程的创建？如何将此数据库选择放入此过程中，以便用户可以提供数据库名称作为此过程的参数？
回答by Martin Smith
If you use EXEC @Var(without brackets - i.e. notEXEC (@Var)) SQL Server looks for a stored procedure matching the name passed in @Var. You can use three part naming for this.
如果您使用EXEC @Var（不带括号 - 即不使用EXEC (@Var)），SQL Server 将查找与传入的名称匹配的存储过程@Var。您可以为此使用三部分命名。
If sys.sp_executesqlis called with a three part name the context is set to the database in which it is called.
如果sys.sp_executesql使用三部分名称调用，则上下文将设置为调用它的数据库。
So you can do this with zeroSQL injection risk as below.
所以你可以在零SQL 注入风险的情况下做到这一点，如下所示。
CREATE PROCEDURE dbo.test @dbname SYSNAME,
@col SYSNAME
AS
SET NOCOUNT, XACT_ABORT ON;
DECLARE @db_sp_executesql NVARCHAR(300) = QUOTENAME(@dbname) + '.sys.sp_executesql'
EXEC @db_sp_executesql N'
SELECT TOP 100 *
FROM sys.columns
WHERE name = @col',
N'@col sysname',
@col = @col
Even if the above wasn't possible I'd still argue that it is perfectly possible to use dynamic SQL for this in a safe manner as here.
即使上述是不可能的，我仍然认为完全可以像这里一样以安全的方式使用动态 SQL。
CREATE PROCEDURE dbo.test
@dbname SYSNAME, /*Use Correct Datatypes for identifiers*/
@col SYSNAME
AS
SET NOCOUNT ON
SET XACT_ABORT ON
IF DB_ID(@dbname) IS NULL /*Validate the database name exists*/
BEGIN
RAISERROR('Invalid Database Name passed',16,1)
RETURN
END
DECLARE @dynsql nvarchar(max)
/*Use QUOTENAME to correctly escape any special characters*/
SET @dynsql = N'USE '+ QUOTENAME(@dbname) + N'
SELECT TOP 100 *
FROM sys.tables
WHERE name = @col'
/*Use sp_executesql to leave the WHERE clause parameterised*/
EXEC sp_executesql @dynsql, N'@col sysname', @col = @col
回答by 3Dave
There are at least two ways to do this:
至少有两种方法可以做到这一点：
Use a case/switch statement (or ,in my example, a naive
if..elseblock) to compare the parameter against a list of databases, and execute a using statement based on that. This has the advantage of limiting the databases that the proc can access to a known set, rather than allowing access anything and everything that the user account has rights to.declare @dbname nvarchar(255); set @dbname = 'db1'; if @dbname = 'db1' use db1; else if @dbname = 'db2' use db2;Dynamic SQL. I HATEdynamic SQL. It's a huge security hole and almost never necessary. (to put this in perspective: In 17 years of professional development, I have never had to deploy a production system which used dynamic SQL). If you decide to go this route, limit the code that is dynamically called/created to a using statement, and a call to another stored proc do do the actual work. You can't just dynamically execute the
usingstatement by itself due to scope rules.declare @sql nvarchar(255); set @sql = 'using '+@dbname+'; exec mydatabase..do_work_proc;';
使用 case/switch 语句（或者，在我的示例中，是一个简单的
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 语句，并调用另一个存储过程来完成实际工作。
using由于范围规则，您不能仅动态执行语句本身。declare @sql nvarchar(255); set @sql = 'using '+@dbname+'; exec mydatabase..do_work_proc;';
of course, in your example, you could just do
当然，在你的例子中，你可以做
set @sql='select * from '+@dbname+'.sys.tables';
the .<schema_name>.resolution operator allows you to query objects in a different database without using a usestatement.
在.<schema_name>.解析运算符可以让您在不同的数据库查询对象，而无需使用use声明。
There are some very, very rare circumstances in which it may be desirable to allow a sproc to use an arbitrary database. In my opinion, the only acceptable use is a code generator, or some sort of database analysis tool which cannot know the required information ahead of time.
在某些非常非常罕见的情况下，可能需要允许 sproc 使用任意数据库。在我看来，唯一可以接受的用途是代码生成器，或者某种无法提前知道所需信息的数据库分析工具。
UpdateTurns out you can't usein a stored procedure, leaving dynamic SQL as the only obvious method. Still, I'd consider using
更新事实证明您不能use在存储过程中使用动态 SQL 作为唯一明显的方法。不过，我会考虑使用
select top 100 * from db_name.dbo.table_name
rather than a use.
而不是use.
回答by JNK
The only way to do this is to use Dynamic SQL, which is powerful but dangerous.
做到这一点的唯一方法是使用Dynamic SQL，它功能强大但很危险。
回答by Roy Latham
A different way to the same end is to use a system stored procedure.
达到相同目的的另一种方法是使用系统存储过程。
See SQL Stored Procedure(s) - Execution From Multiple Databases.
If the procedure name starts with "sp_", is in the master db and marked with sys.sp_MS_MarkSystemObject, then it can be invoked like this:
如果过程名称以“sp_”开头，在主数据库中并用 sys.sp_MS_MarkSystemObject 标记，则可以像这样调用它：
Exec somedb.dbo.Test;
Exec anotherdb.dbo.Test;
Or like this:
或者像这样：
Declare @Proc_Name sysname;
Set @Proc_Name = 'somedb.dbo.Test';
Exec @Proc_Name;
Parameters can be used too.
也可以使用参数。
Using this technique requires using the 'sp_' prefix and putting code into a system database. It is your choice if that offsets not using dynamic SQL.
使用此技术需要使用“sp_”前缀并将代码放入系统数据库中。如果偏移量不使用动态 SQL，则由您选择。
