Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Проблема с вызовом функции возвращающей табличное значение из хранимой процедуры  [new]
Bandicoot_NSK
Member

Откуда: Сибирь
Сообщений: 8
Здравствуйте. Стоит MS SQL 2008 R2
Есть функция, возвращающая табличное значение
CREATE FUNCTION dbo.fGetMonthRatio
(
    @ReportDate DATE
)

RETURNS @RetTable TABLE
(
    id INT
    ,ratio FLOAT
)

AS
BEGIN
    INSERT @RetTable
        SELECT
            id,
            ratio
        FROM
            ...
            много кода с JOIN и прочими штуками
            ...
    RETURN
END


Эту функцию я вызываю из хранимой процедуры:
ALTER PROCEDURE dbo.pTestProcedure
    @ReportDate DATE
AS
BEGIN
    SELECT * FROM dbo.fGetMonthRatio(DATEADD(MONTH, -6, @ReportDate))
END


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

Нашел точную причину, из-за чего запрос зависает:
Когда я вызываю функцию из запроса, я передаю ей параметр @ReportDate, перед этим вызывая над ним функцию DATEADD.
SELECT * FROM dbo.fGetMonthRatio(DATEADD(MONTH, -6, @ReportDate))

Если вместо @ReportDate написать например '2014-08-01', функция отлично работает. Когда вместо литерала в функцию подается переменная - все ломается.

Помогите пожалуйста исправить
4 сен 14, 16:31    [16536034]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с вызовом функции возвращающей табличное значение из хранимой процедуры  [new]
Bandicoot_NSK
Member

Откуда: Сибирь
Сообщений: 8
Поправка. Проблему я решил, объявив еще одну переменную и присваивая ей значение @ReportDate при создании
DECLARE
    @TempDate DATE = @ReportDate


Однако вопрос остается. Почему я не могу передавать в функцию переменную, которую передал в процедуру?
4 сен 14, 16:38    [16536078]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с вызовом функции возвращающей табличное значение из хранимой процедуры  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Bandicoot_NSK,

потому что аргументом функции и процедуры не может быть сложное выражение,
например, вызов встроенной функции.

Только литеральные константы, переменные и поля таблиц (в APPLY).
В скалярную функцию можно передать поле и без APPLY.
4 сен 14, 16:41    [16536098]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с вызовом функции возвращающей табличное значение из хранимой процедуры  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
Bandicoot_NSK,

http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx
4 сен 14, 16:42    [16536103]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с вызовом функции возвращающей табличное значение из хранимой процедуры  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Bandicoot_NSK
Если вместо @ReportDate написать например '2014-08-01', функция отлично работает.
А вообще, я тоже кое в чём бывает ошибаюсь...
4 сен 14, 16:44    [16536111]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с вызовом функции возвращающей табличное значение из хранимой процедуры  [new]
Bandicoot_NSK
Member

Откуда: Сибирь
Сообщений: 8
Shakill,

большое спасибо за статью! Очень полезная, я про опции даже и не знал.
В такие моменты понимаешь, что теоретической подготовки прям не хватает
5 сен 14, 07:46    [16538007]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить