SQL Server:如何在存储过程中获取数据库名称作为参数 [英] SQL Server: how to get a database name as a parameter in a stored procedure

问题描述

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

I'm trying to create a simple stored procedure which queries a sys.tables table.

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

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

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?

推荐答案

EDIT

我的回答假设了一些使这种方法无效的事情.不幸的是，SO 不会让我删除答案.我推荐@MartinSmith 的回答(在这个帖子下面).我认为这里仍然有一些有用的信息，但它实际上并没有解决原始问题.神速.

EDIT

My answer assumes some things which make this approach effectively useless. Unfortunately, SO will not let me delete the answer. I recommend @MartinSmith's answer (below in this thread). I think there's still some useful information here, BUT it doesn't actually solve the original problem. Godspeed.

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

There are at least two ways to do this:

1. 使用 case/switch 语句(或者，在我的示例中，是一个简单的 if..else 块)将参数与数据库列表进行比较，并执行基于 using 语句在那.这样做的好处是将 proc 可以访问的数据库限制为已知集合，而不是允许访问用户帐户有权访问的任何内容.

1. Use a case/switch statement (or ,in my example, a naive if..else block) 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;

动态 SQL.我讨厌动态 SQL.这是一个巨大的安全漏洞，几乎从来没有必要.(正确地说:在 17 年的专业开发中，我从未部署过使用动态 SQL 的生产系统).如果您决定走这条路线，请将动态调用/创建的代码限制为 using 语句，并调用另一个存储过程来完成实际工作.由于范围规则，您不能仅动态执行 using 语句本身.

Dynamic SQL. I HATE dynamic 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 using statement by itself due to scope rules.

 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';

.. 解析运算符允许您在不使用 use 语句的情况下查询不同数据库中的对象.

the .<schema_name>. resolution operator allows you to query objects in a different database without using a use statement.

在某些非常非常罕见的情况下，可能需要允许 sproc 使用任意数据库.在我看来，唯一可以接受的用途是代码生成器，或者某种无法提前知道所需信息的数据库分析工具.

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.

更新 事实证明，您不能在存储过程中使用，而将动态 SQL 作为唯一明显的方法.不过，我会考虑使用

Update Turns out you can't use in a stored procedure, leaving dynamic SQL as the only obvious method. Still, I'd consider using

select top 100 * from db_name.dbo.table_name

而不是use.

这篇关于SQL Server:如何在存储过程中获取数据库名称作为参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！