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

Это не работает, потому что я должен поместить GO после USE @dbname, но это завершает создание этой процедуры? Как я могу поместить эту всплывающую базу данных в эту процедуру, чтобы пользователь мог указать имя базы данных в качестве параметра для этого процесса?

Ответы

Ответ 1

Существует как минимум два способа сделать это:

Используйте оператор 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 '[email protected]+'; exec mydatabase..do_work_proc;';
```

конечно, в вашем примере вы могли бы просто сделать

    set @sql='select * from '[email protected]+'.sys.tables';

Оператор разрешения .<schema_name>. позволяет запрашивать объекты в другой базе данных без использования оператора use.

Есть очень, очень редкие обстоятельства, в которых может быть желательно разрешить sproc использовать произвольную базу данных. На мой взгляд, единственным приемлемым использованием является генератор кода или какой-то инструмент анализа базы данных, который заранее не может знать требуемую информацию.

Обновление. Выключено, вы не можете use в хранимой процедуре, оставив динамический SQL как единственный очевидный метод. Тем не менее, я бы подумал об использовании

select top 100 * from db_name.dbo.table_name

а не use.

Ответ 2

Если вы используете EXEC @Var (без скобок - т.е. не EXEC (@Var)), SQL Server ищет хранимую процедуру, соответствующую имени, переданному в @Var. Вы можете использовать для этого три имени.

Если sys.sp_executesql вызывается с именем трех частей, контекст устанавливается в базу данных, в которой он вызывается.

Итак, вы можете сделать это с помощью zero 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

Даже если выше было невозможно, я бы все же утверждал, что вполне возможно использовать динамический 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

Ответ 3

Единственный способ сделать это - использовать Dynamic SQL, который является мощным, но опасным.

Сначала прочитайте эту статью.

Ответ 4

Другой подход к тому же концу - использовать хранимую процедуру системы.

См. Хранимые процедуры SQL - выполнение из нескольких баз данных.

Если имя процедуры начинается с "sp_", находится в master db и помечено с помощью sys.sp_MS_MarkSystemObject, тогда его можно вызвать следующим образом:

Exec somedb.dbo.Test;
Exec anotherdb.dbo.Test;

Или вот так:

Declare @Proc_Name sysname;
Set @Proc_Name = 'somedb.dbo.Test';
Exec @Proc_Name;

Также могут использоваться параметры.

Использование этого метода требует использования префикса 'sp_' и ввода кода в системную базу данных. Это ваш выбор, если это не позволяет использовать динамический SQL.