t-sql udf для возврата объединенных значений полей для одной строки в любой заданной таблице

Question

Я ищу, чтобы создать функцию, где передается имя таблицы и значение идентификатора, и процедура возвращает одну строку со всеми значениями поля для этой указанной строки. Все таблицы, на которые можно ссылаться, имеют поле с именем "Id". Я нашел другие примеры, которые могут объединить указанную строку в таблице, но не смогли найти способ динамического определения таблицы. Ниже был самый близкий пример, который я нашел здесь:

TSQL: значения всех полей подряд в одну строку

CREATE FUNCTION udf_GetTableValues (@TableName varchar(MAX) = '',@Id int = 0)
    RETURNS varchar(MAX)
AS
BEGIN
  DECLARE @returnVal varchar(MAX)

  SELECT @ReturnVal = ''
  SELECT @ReturnVal = @ReturnVal 
         + ' // ' 
         + T2.N.value('local-name(.)', 'nvarchar(128)')
         +': '
         + T2.N.value('.', 'nvarchar(max)')
    FROM (SELECT *
            FROM Branch 
           WHERE ID = 1
             FOR XML PATH(''), type) as T1(X)
   CROSS APPLY T1.X.nodes('/*') as T2(N)


  RETURN @ReturnVal  
END

Answer 1

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

Вот методика применения динамической логики SQL к каждой строке таблицы

• Создайте временную таблицу для хранения всех входных параметров для вашего динамического SQL, а также ожидаемых результатов
• Создайте хранимую процедуру, которая уже ожидает, что временная таблица существует, и зацикливается на строках временной таблицы, применяя динамическую логику SQL.
• Добавьте данные, которые вы хотите оценить, во временную таблицу
• Выполните ваш динамический процесс SQL
• Наслаждайтесь ожидаемыми результатами:-)

1) Начнем с настройки некоторых тестовых данных.

    -- ====================================================================
    -- BEGIN: Setup test data
    -- ====================================================================
    IF OBJECT_ID('Branch', 'U') IS NOT NULL DROP TABLE Branch;
    IF OBJECT_ID('Branch2', 'U') IS NOT NULL DROP TABLE Branch2;
    GO
    CREATE TABLE Branch (
        ID      int
       ,Column1 varchar(20)
       ,Column2 varchar(20)
       ,Column3 varchar(20)
    )
    CREATE TABLE Branch2 (
        ID      int
       ,Column1 varchar(20)
       ,Column2 varchar(20)
       ,Column3 varchar(20)
    )
    SET NOCOUNT ON
    INSERT Branch SELECT 1, 'Hello', 'World', 'I write SQL'
    INSERT Branch SELECT 2, 'I like', 'it to be', 'complicated'

    INSERT Branch2 SELECT 1, 'Hello', 'World', 'I write SQL'
    INSERT Branch2 SELECT 2, 'I like', 'it to be', 'complicated'
    SET NOCOUNT OFF
    GO
    -- ====================================================================
    -- END: Setup test data
    -- ====================================================================

2) Далее мы создадим процесс, который обрабатывает динамический SQL.

    -- ====================================================================
    -- BEGIN: Proc with dynamic SQL logic
    -- ====================================================================
    IF OBJECT_ID('prcReturnTableData', 'P') IS NOT NULL DROP PROCEDURE prcReturnTableData;
    GO
    CREATE PROCEDURE prcReturnTableData 
    AS

    DECLARE @SQL                    nvarchar(MAX)
           ,@ParameterDefinitions   nvarchar(MAX)
           ,@TableName              varchar(128)
           ,@ID                     int
     SELECT @ParameterDefinitions   = '@ID int'      

    DECLARE @idx int
     SELECT @idx = MIN(idx) FROM #Result

    WHILE @idx IS NOT NULL
      BEGIN
        SELECT @TableName = TableName
              ,@ID        = ID
          FROM #Result
         WHERE idx = @idx

        SELECT @SQL = '
            DECLARE @ReturnVal varchar(MAX)

            SELECT @ReturnVal = ''''
            SELECT @ReturnVal = @ReturnVal 
                  + '' // '' 
                  + T2.N.value(''local-name(.)'', ''nvarchar(128)'')
                  + '': ''
                  + T2.N.value(''.'', ''nvarchar(max)'')
              FROM (
                    SELECT *
                      FROM ' + @TableName + ' 
                     WHERE ID = @ID
                       FOR XML PATH(''''), type
                    ) as T1(X)
             CROSS APPLY T1.X.nodes(''/*'') as T2(N)


             UPDATE #Result SET ReturnVal = @ReturnVal WHERE ID = @ID';

        EXEC sp_executeSQL @SQL, @ParameterDefinitions, @ID = @ID;

        SELECT @idx = MIN(idx) FROM #Result WHERE idx > @idx
    END
    GO
    -- ====================================================================
    -- END: Proc with dynamic SQL logic
    -- ====================================================================

3) Наконец, давайте выполним процесс и получим результаты, которые мы хотим.

    -- ====================================================================
    -- BEGIN: Solution to execute dynamic SQL logic on each row of a table
    -- ====================================================================
    -- Create the temp table that the dynamic SQL proc expects
    IF OBJECT_ID('tempdb..#Result', 'U') IS NOT NULL DROP TABLE #Result;
    CREATE TABLE #Result (idx int IDENTITY(1,1), TableName varchar(128) NOT NULL, ID int NOT NULL, ReturnVal varchar(MAX) NULL);

    -- Popluate the temp table with the rows you want to evaluate
    INSERT #Result (TableName, ID)
    SELECT 'Branch', ID FROM Branch
    INSERT #Result (TableName, ID)
    SELECT 'Branch2', ID FROM Branch2

    -- Results before
    SELECT * FROM #Result

    -- Execute the dynamic SQL proc
    EXEC prcReturnTableData

    -- Results after
    SELECT * FROM #Result
    -- ====================================================================
    -- END: Solution to execute dynamic SQL logic on each row of a table
    -- ====================================================================