Можно ли (и как) передать в функцию вложенный запрос?

#sql #sql_server #sql_server_2005

Суть проблемы. Есть функция, на входе у которой должны быть id объектов определенной
таблицы.

Пока я нашел 3 способа решения, но они мне не очень нравятся:


Передать строку и распарсить. Не нравится, потому что это ударит по производительности.
К тому же на передающем конце придется сначала эту строку сформировать из запроса SELECT
id FROM table1 WHERE 
Использовать временную таблицу. То есть создать ее там, где буду вызывать свою функцию
и в функции уже использовать ее значения. Неудобно, потому что использование функции
будет не очевидным.
Передать SQL запрос в виде строки, а в функции уже выполнить его. Просто мне так
не нравится :)


Есть еще варианты, которые не усложнят читаемость кода и не будут тормозить?
    

Ответы

Ответ 1

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


Начиная с 2016 сервера можно передать список ИДов в формате JSON.

В 2005, в свою очередь, можно передать в виде XML.

UPD: Есть ещё один интересный способ.
Можно создать польовательский тип данных таблицу, наполнить её содержимым и передать
как параметр функции:

--Чистим за собой (чтобы повторный запуск не приводил к ошибке)
IF OBJECT_ID('FINT', 'IF') IS NOT NULL DROP FUNCTION FINT;
IF TYPE_ID('TINT') IS NOT NULL DROP TYPE TINT;
GO
--Создаём пользровательский тип переменная-таблица
CREATE TYPE TINT AS TABLE (
  id INT
);
GO
--Создаём функцию
CREATE FUNCTION FINT (
  @tint TINT READONLY
)
RETURNS TABLE
AS
RETURN(
  SELECT id, 1000+id [1000+id] FROM @tint
)
GO
--Илдлюстрируем вызов функции с параметром таблицы
DECLARE
  @tint TINT

INSERT @tint SELECT 1
INSERT @tint SELECT 2
INSERT @tint SELECT 3

SELECT *
FROM dbo.FINT(@tint)

--Результат:

/*
id  1000+id
1   1001
2   1002
3   1003
*/


Если подобная задача передать список INT'ов встаёт часто, то вполне элегантное решение.


Ответ 2

Даже если не нравится(п.3), но запрос передать все-таки хочется, то можно CLRку создать. :)

namespace CLR_Functions
{
    public partial class UserDefinedFunctions
    {
        [Microsoft.SqlServer.Server.SqlFunction(
            IsDeterministic = true,
            IsPrecise = true,
            DataAccess = DataAccessKind.Read,
            SystemDataAccess = SystemDataAccessKind.Read)]
        //  --  ALTER DATE: 29.09.2014 09:41 d-ivanov; 25.09.2014 16:22 d-ivanov;
        public static SqlString SFs_C_Name_ID_CD_Parameter_Values(SqlGuid F_Parameter_Values)
        {

        var result = "";


        //  Получение имени таблицы, раздела и допполя.
        using (var conn = new SqlConnection("context connection=true"))
        {
            conn.Open();

            var cmd = new SqlCommand(
            @"
                SELECT
                    @F_Division                 = cpv.F_Division,               --
 Дивижн записи.
                    @F_Custom_Table_LINK        = cpv.F_Custom_Table_LINK,      --
 Числовой линк.
                    @F_Custom_Table_LINKG       = cpv.F_Custom_Table_LINKG,     --
 Гуидовский линк.
                    @C_CCF_Display_Name         = ccf.C_Display_Name,           --
 Имя допполя.
                    @C_CCF_System_Name          = ccf.C_System_Name,            --
 Имя допполя системное.
                    @C_CFS_Name                 = ccfs.C_Name,                  --
 Имя раздела.
                    @C_Table_Name_Real          = ccft.C_Table_Name_Real        --
 Имя обекта, таблицы или представления.
                FROM dbo.CD_Parameter_Values cpv
                INNER JOIN dbo.CS_Custom_Fields ccf
                    ON  cpv.F_Custom_Field          = ccf.LINK
                INNER JOIN dbo.CS_Custom_Field_Sections ccfs
                    ON  ccf.F_Custom_Field_Sections = ccfs.LINK
                INNER JOIN dbo.CS_Custom_Fileld_Tables ccft
                    ON  ccfs.F_Custom_Fileld_Tables = ccft.LINK
                WHERE   cpv.LINK    = @F_Parameter_Values

            ", conn);
            //  Входные параметры.
            cmd.Parameters.Add("@F_Parameter_Values", SqlDbType.UniqueIdentifier).Direction
= ParameterDirection.Input;
            //  Выходные параметры.
            cmd.Parameters.Add("@F_Division", SqlDbType.TinyInt).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@F_Custom_Table_LINK", SqlDbType.Int).Direction =
ParameterDirection.Output;
            cmd.Parameters.Add("@F_Custom_Table_LINKG", SqlDbType.UniqueIdentifier).Direction
= ParameterDirection.Output;
            cmd.Parameters.Add("@C_CCF_Display_Name", SqlDbType.NVarChar, 200).Direction
= ParameterDirection.Output;
            cmd.Parameters.Add("@C_CCF_System_Name", SqlDbType.NVarChar, 200).Direction
= ParameterDirection.Output;
            cmd.Parameters.Add("@C_CFS_Name", SqlDbType.NVarChar, 200).Direction
= ParameterDirection.Output;
            cmd.Parameters.Add("@C_Table_Name_Real", SqlDbType.NVarChar, 100).Direction
= ParameterDirection.Output;

            //  Пушим линк допполя. Push me, and then just touch... (",)
            cmd.Parameters["@F_Parameter_Values"].Value = F_Parameter_Values;

            //  Выполняем запрос.
            cmd.ExecuteNonQuery();
}
}
}
}


Потом можно считать из запроса(ну до закрывающих фигурных скобок конечно же):

var C_CCF_Display_Name = cmd.Parameters["@C_CCF_Display_Name"].Value.ToString();


Это просто пример как написать. В функцию передаем свой запрос. :)

Заранее прошу прощения, что не написал отдельный абстрактный пример, а просто код
свой кинул. Много работы. Заглянул мельком. Всем бобра. :)