Присоединение расширенных свойств к набору результатов хранимой процедуры

Почему бы не вернуть 2 набора результатов из SP? Один из них — это фактический набор результатов, который у вас есть сейчас, а другой — это метаданные.

Таблица метаданных может быть табличной переменной, созданной в SP, со следующей структурой:

DECLARE @ResultsMetadata AS TABLE
(
  Id INT NOT NULL IDENTITY(1,1),
  ColumnName VARCHAR(128) NOT NULL,
  ColumnMetadata VARCHAR(128) NOT NULL
)

Вы можете прочитать это в том, что я предполагаю, это CLR (из-за тега). Легко читать несколько наборов результатов с помощью SqlDataReader. Если вам необходимо дополнительно обработать результаты также в SQL, то, возможно, вы можете переключиться на вывод XML с двумя элементами верхнего уровня (основной набор результатов и метаданные).

ИЗМЕНИТЬ

На самом деле только что заметил, что вы читаете это с помощью ADO.NET, поэтому у вас не должно быть проблем с несколькими наборами результатов. Вы можете использовать SqlDataReader.NextResult. чтобы перейти ко второму набору результатов (метаданные).

Предлагаемый метод 1:

сначала создайте тип метаданных:

CREATE TYPE MetaData AS TABLE
(
  Name VARCHAR(128) NOT NULL,
  Property NVARCHAR(Max)
)

В каждой хранимой процедуре возвращайте свои метаданные после набора результатов вашей хранимой процедуры:

CREATE PROCEDURE YourProcedure 
AS BEGIN
    --FIRST Result Set
    --Your Stored Procedure code here
    ...


    --Second Result Set
    DECLARE @MetaData dbo.MetaData 
    INSERT INTO @MetaData(Name, Property)VALUES(...)
    SELECT * FROM @MetaData
END

Вы можете прочитать свой набор результатов в коде отдельно. следующий код представляет собой код C# для получения нескольких наборов результатов из одной хранимой процедуры.

SqlConnection oConn = null;
DataSet dsReturn = null;
try
{
    getConnection(ref oConn, 1);

    using (SqlStoredProcedure sspObj = new SqlStoredProcedure("dbo.YourStoredProcedure", oConn, CommandType.StoredProcedure))
    {
        sspObj.AddParameterWithValue("@Parameter", SqlDbType.Int, Parametervalue, ParameterDirection.Input, type);
        dsReturn = sspObj.ExecuteDataSet();
        //You don't need Dispose() - because the using will do that on sspObj
    }

    closeConnection(ref oConn);
}
catch (Exception xObj)
{
    dsReturn = new DataSet("Empty");
}

В приведенном выше коде dsReturn.Tables[0] — это результирующий набор вашей хранимой процедуры, а dsReturn.Tables[1] — результирующий набор метаданных вашей хранимой процедуры.

Предлагаемый метод 2:

Добавьте все метаданные, которые хотят расширить свойство вашей хранимой процедуры:

EXEC sys.sp_addextendedproperty @name=N'MetaDataColumn1', @value=N'value1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'YourProcedure'
GO
EXEC sys.sp_addextendedproperty @name=N'MetaDataColumn2', @value=N'value2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'YourProcedure'
GO

После запроса верните метаданные вашей хранимой процедуры:

SELECT ep.name, ep.value
FROM sys.extended_properties ep
INNER JOIN sys.procedures p ON p.object_id = ep.major_id
WHERE p.name = 'YourProcedure'
    AND ep.name LIKE 'MetaData%'

вы можете выполнить вышеуказанный запрос из своего кода (пример С#):

connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
sql = "AboveQuery";
sqlCnn = new SqlConnection(connetionString);
sqlCnn.Open();
sqlCmd = new SqlCommand(sql, sqlCnn);
SqlDataReader sqlReader = sqlCmd.ExecuteReader();