Блог

Главная - Вопросы по программированию - Передача таблицы и сервера базы данных в хранимую процедуру

Передача таблицы и сервера базы данных в хранимую процедуру

#sql #sql-server #sql-server-2017

#sql #sql-сервер #sql-server-2017

Вопрос:

У меня есть два экземпляра базы данных, и мне нужно передать данные между ними. Я хочу сделать это в хранимой процедуре. Возможно ли передать целевую таблицу (и экземпляр базы данных) в качестве параметра хранимой процедуре?

У меня получилось:

 INSERT INTO [SERVER1].[MYSCHEMA1].[TargetTable]
    ([ID], [Timestamp])
SELECT 
    [ID], [Timestamp]
FROM [SERVER2].[MYSCHEMA2].[SourceTable]
WHERE ID= @SomeId

Но я хочу сделать что-то вроде этого:

 CREATE PROCEDURE spDoStuff
    -- Add the parameters for the stored procedure here
     @id uniqueidentifier,
     @server nvarchar(100),
     @table nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO [SERVER1].[MYSCHEMA1].[TargetTable]
        ([ID], [Timestamp])
    SELECT 
        [ID], [Timestamp]
    FROM @server.@table.[SourceTable]
    WHERE ID= @id
END

Я знаю, что это можно сделать с помощью динамического SQL, но я хотел бы избежать этого, если это возможно. Я также читал о TVP, но я не уверен, что это действительно необходимо, поскольку это потребовало бы, чтобы я также разработал таблицу в коде. Это будет вызвано из Entity Framework Core 2.2.

Комментарии:

1. Вы не можете избежать динамического SQL для выполнения этого типа работы. Почему вы хотите этого избежать?

2. Это самое личное мнение о его «беспорядке» и удобочитаемости. Но это все. Если другого способа нет, я воспользуюсь им.

3. При условии, что вы хорошо пишете свой динамический SQL, он будет таким же «беспорядочным» или «нечитаемым», как и другой SQL. длинный оператор SQL, написанный в одной строке, трудно читать, независимо от того, динамический он или нет. Для чего-то столь простого, как передача динамической таблицы и имени базы данных, я был бы удивлен, если бы вам удалось сделать SQL беспорядочным или иметь низкую читаемость; вам пришлось бы намеренно усложнить чтение, чтобы добиться этого.

4. @Larnu — Последующий вопрос. 🙂 При использовании динамического sql есть ли хороший способ предотвратить внедрение sql при передаче строки, такой как @server, в мой sql? Или quotename это правильный путь?

5. QUOTENAME это способ использовать динамические имена объектов. таким образом SET @SQL = N'SELECT * FROM dbo.' QUOTENAME(@TableName) N';'; прекратится внедрение, так как все символы будут корректно экранированы. Также помогает правильное использование типов данных; например, использование sysname (эквивалент nvarchar(128) ) для типа данных параметров означает, что можно обслуживать каждое имя объекта, поскольку ограничивает количество символов, которые могут быть введены. Но все же, QUOTENAME это обязательно.

Ответ №1:

Для завершения я бы написал ваш SP следующим образом:

 CREATE PROCEDURE spDoStuff
    -- Add the parameters for the stored procedure here
     @id uniqueidentifier,
     @server sysname,
     @table sysname
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL nvarchar(MAX);
    SET @SQL = N'INSERT INTO [SERVER1].[MYSCHEMA1].[TargetTable]'   NCHAR(13)   NCHAR(10)  
               N'    ([ID], [Timestamp])'   NCHAR(13)   NCHAR(10)  
               N'SELECT [ID], [Timestamp]'   NCHAR(13)   NCHAR(10)  
               N'FROM '   QUOTENAME(@server)   N'.'   QUOTENAME(@table)   N'.SourceTable'   NCHAR(13)   NCHAR(10)   --Seems Odd should @Table actually be @database?
               N'WHERE ID = @ID;';

    EXEC sp_executesql @SQL, N'@id uniqueidentifier', @id = @id;
END