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

Откуда:
Сообщений: 9
Добрый день,
есть table valued function, в которую я хочу передавать различное количество ид (брендов) для построения отчётности.
Выглядит она примерно так:
CREATE TYPE IntArray AS TABLE (n int NOT NULL)

CREATE FUNCTION dbo.GetChainCheckins (
     @RequestedChainName VARCHAR(64)
    ,@RequestedBrandsIds IntArray READONLY
    ,@RequestedDateFrom DATE
    ,@RequestedDateTill DATE)
RETURNS TABLE
AS RETURN
(
	SELECT
		@RequestedDateFrom          AS PERIOD_START
		,@RequestedDateTill         AS PERIOD_END
		,@RequestedChainName        AS 'Chain'
		,COUNT(DISTINCT R.SHOP_ID) AS NUM_UNIQUE_CHECKINS
	FROM
        LOGS AS R

	WHERE
		R.DATE >= @RequestedDateFrom AND R.DATE <= @RequestedDateTill
		AND R.BRAND_ID in (SELECT n FROM @RequestedBrandsIds)
)

GO



Не могу разобраться, как теперь правильно вызывать эту функцию?
Наивный подход не работает:

SELECT * FROM dbo.[GetChainCheckins](
    'Letoile',
    (SELECT 7 AS Id                 -- Vichy
    UNION SELECT 8                -- La Roche-Posay
    UNION SELECT 19              -- CeraVe
    UNION SELECT 17              -- Other DCA
    ),
    @RequestedDateFrom,
    @RequestedDateTill
);



Как правильно сформировать список из 4 брендов и передать его в функцию?

Спасибо
6 сен 18, 12:54    [21666815]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36694
Как-то так:

declare @t IntArray 

insert @t
SELECT 7 AS Id                 -- Vichy
    UNION SELECT 8                -- La Roche-Posay
    UNION SELECT 19              -- CeraVe
    UNION SELECT 17              -- Other DCA


SELECT * FROM dbo.[GetChainCheckins](
    'Letoile',
    @t,
    @RequestedDateFrom,
    @RequestedDateTill
);
6 сен 18, 12:56    [21666816]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
sql_guru2,

https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017
6 сен 18, 12:56    [21666817]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
sql_guru2
Member

Откуда:
Сообщений: 9
Гавриленко Сергей Алексеевич,

а можно это как-то инлайн сделать? Т.к. вызов сам по себе находится в TVF, и RETURN TABLE определяется автоматически. Если я начну добавлять DECLARE, то мне придется в ручную расписывать тип возвращаемой таблицы
6 сен 18, 13:02    [21666830]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
sql_guru2,

сделайте обычную инлайновую функцию и соединяйте со своим селектом
6 сен 18, 13:54    [21666938]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
точнее, зачем функция нужна вообще не ясно
6 сен 18, 14:00    [21666955]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
sql_guru2
Member

Откуда:
Сообщений: 9
TaPaK,

Functions can only be inlined when they consist of a single SELECT statement which returns a result set.

А выше предлагается сделать subquery
6 сен 18, 14:20    [21666985]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
sql_guru2
TaPaK,

Functions can only be inlined when they consist of a single SELECT statement which returns a result set.

А выше предлагается сделать subquery

ээээ что?
6 сен 18, 14:21    [21666987]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
sql_guru2
Member

Откуда:
Сообщений: 9
TaPaK,

Вот из-за этого не получается сделать inline функцию, которая вызывает этот код:
insert @t SELECT ... 
6 сен 18, 14:29    [21667007]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
sql_guru2,

а просто сделатьселект из LOGS вам религиозные или какие убеждения не позовляют?
6 сен 18, 14:32    [21667011]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
KRS544
Member

Откуда:
Сообщений: 497
sql_guru2
TaPaK,

Вот из-за этого не получается сделать inline функцию, которая вызывает этот код:
insert @t SELECT ... 

Это код не функции, а вызова этой функции
6 сен 18, 14:38    [21667018]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
sql_guru2
Member

Откуда:
Сообщений: 9
KRS544,

Этот код будет лежать в своей, отдельной TVF.


TaPaK,

Этот сниппет будет использоваться в 40 отчетах, а копипастить 40 раз мне религия не позволяет, да.
6 сен 18, 15:42    [21667134]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
sql_guru2
KRS544,

Этот код будет лежать в своей, отдельной TVF.


TaPaK,

Этот сниппет будет использоваться в 40 отчетах, а копипастить 40 раз мне религия не позволяет, да.

т.е. 40 раз писать INSERT это нормально
6 сен 18, 15:50    [21667148]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
sql_guru2
Member

Откуда:
Сообщений: 9
TaPaK,

Изначально вопрос был как сделать SELECT прямо в списоке параметров.
6 сен 18, 16:06    [21667173]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
sql_guru2
TaPaK,

Изначально вопрос был как сделать SELECT прямо в списоке параметров.

уже ответили. Тот бред вашей функции сделать инлайн без
"AND R.BRAND_ID in (SELECT n FROM @RequestedBrandsIds)"
и

SELECT * FROM dbo.<ваша бредовая функция>(    'Letoile',  @RequestedDateFrom,    @RequestedDateTill)
WHERE 
R.BRAND_ID in 
		(
			SELECT 7 AS Id                 -- Vichy
			UNION SELECT 8                -- La Roche-Posay
			UNION SELECT 19              -- CeraVe
			UNION SELECT 17              -- Other DCA
		)
6 сен 18, 16:10    [21667179]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
ну и гуру, же понимает тупиковость и проблемность табличных типов
6 сен 18, 16:12    [21667183]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
sql_guru2
Member

Откуда:
Сообщений: 9
TaPaK,

В другом отчёте мне нужно 2 других бренда.
В теле отчета я хочу сделать:
    ...
    CROSS APPLY
        dbo.<моя бредовая функция>(@ChainName, select 22 id union select 44, @RequestedDateFrom, @RequestedDateTill) AS AGGREGATE_BRAND_INFO


Понимаете, как сильно усложнится тело отчета (там еще 20 подобных параметров), если использовать ваш подход?
6 сен 18, 16:16    [21667187]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
sql_guru2
Member

Откуда:
Сообщений: 9
TaPaK,

TaPaK
ну и гуру, же понимает тупиковость и проблемность табличных типов

Какие подводные камни могут быть? Расскажите.
6 сен 18, 16:20    [21667195]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
    CROSS APPLY
        dbo.<моя бредовая функция>(@ChainName, select 22 id union select 44, @RequestedDateFrom, @RequestedDateTill) AS AGGREGATE_BRAND_INFO

тут только к психиатору
6 сен 18, 16:22    [21667198]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
invm
Member

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

Хотите инлайновости - передавайте список значений через xml:
CREATE FUNCTION dbo.GetChainCheckins (
     @RequestedChainName VARCHAR(64)
    ,@RequestedBrandsIds xml
    ,@RequestedDateFrom DATE
    ,@RequestedDateTill DATE)
RETURNS TABLE
AS RETURN
(
	SELECT
		@RequestedDateFrom          AS PERIOD_START
		,@RequestedDateTill         AS PERIOD_END
		,@RequestedChainName        AS 'Chain'
		,COUNT(DISTINCT R.SHOP_ID) AS NUM_UNIQUE_CHECKINS
	FROM
        LOGS AS R

	WHERE
		R.DATE >= @RequestedDateFrom AND R.DATE <= @RequestedDateTill
		AND R.BRAND_ID in (SELECT t.n.value('@n', 'int') FROM @RequestedBrandsIds.nodes('item') t(n))
)

Вызов
...
    CROSS APPLY
        dbo.GetChainCheckins (@ChainName, '<item n = "22"/><item n = "44"/>', @RequestedDateFrom, @RequestedDateTill) AS AGGREGATE_BRAND_INFO


Если всегда нужно передавать список целых чисел, то можно просто строкой '22,44' и внутри задействовать какой-либо вариант функции распарсивания строки, вариантов которых на форуме полно.
6 сен 18, 16:25    [21667205]     Ответить | Цитировать Сообщить модератору
 Re: Передача Table-Valued Paramter в TVF  [new]
sql_guru2
Member

Откуда:
Сообщений: 9
invm,

Спасибо больше, с xml получается как раз, как я хотел!
6 сен 18, 16:35    [21667222]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить