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

Откуда:
Сообщений: 7
Динамические запросы вещь удобная, но их формирование и дальнейшая работа внутри функции нереализуема (или нет?), т.к. "Выполнение запроса внутри функции может привести к нарушению основного контракта UDF - не изменять состояния базы данных. Ничто не мешает написать DROP внутри EXEC'a, что приведет к нарушению этого контракта."

Собственно есть один вопрос: можно ли сформировать динамический запрос и запустить его внутри функции без использования EXEC? Приму в дар любые варианты.

По сути запрос простой и выглядит примерно так
'select' +  @column + ' from table'
, где @column - имя поля, которое меняется.

Сообщение было отредактировано: 10 фев 21, 21:09
10 фев 21, 21:14    [22279163]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
vikkiv
Member

Откуда: EU
Сообщений: 2916
ol_chig,

а) чтобы выполнить динамический запрос нужно запустить процедуру,
б) процедуры внутри UDF функций не поддерживаются (за очень специфичным исключением)
в) ещё можно через свою CLR конечно.

впросы ко время исправления внизу? написал скрипт машины времени.

Сообщение было отредактировано: 10 фев 21, 21:43
10 фев 21, 21:47    [22279171]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37146
ol_chig
Приму в дар любые варианты.
Наймите архитектора, который вам расскажет, что надо или можно делать, а что нет. Можно делать, когда фантазировать надоест.
11 фев 21, 03:28    [22279246]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
iap
Member

Откуда: Москва
Сообщений: 47049
vikkiv
ol_chig,

а) чтобы выполнить динамический запрос нужно запустить процедуру
Необязательно
11 фев 21, 10:01    [22279314]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
aleks222
Member

Откуда:
Сообщений: 1237
ol_chig
Динамические запросы вещь удобная, но их формирование и дальнейшая работа внутри функции нереализуема (или нет?), т.к. "Выполнение запроса внутри функции может привести к нарушению основного контракта UDF - не изменять состояния базы данных. Ничто не мешает написать DROP внутри EXEC'a, что приведет к нарушению этого контракта."

Собственно есть один вопрос: можно ли сформировать динамический запрос и запустить его внутри функции без использования EXEC? Приму в дар любые варианты.

По сути запрос простой и выглядит примерно так
'select' +  @column + ' from table'
, где @column - имя поля, которое меняется.


1. Количество полей в таблице априорно ограничено.
2.
if @column  = 'column1' select column1 from table
else if @column  = 'column2' select column2 from table
...


3. дерзайте.

Сообщение было отредактировано: 11 фев 21, 10:25
11 фев 21, 10:31    [22279342]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
ol_chig
Member

Откуда:
Сообщений: 7
iap
vikkiv
ol_chig,

а) чтобы выполнить динамический запрос нужно запустить процедуру
Необязательно

Сейчас я в переменную собираю запрос и выполняю его через EXEC, который нельзя использовать внутри функции.
Подскажите как еще?

aleks222

1. Количество полей в таблице априорно ограничено.
2.
if @column  = 'column1' select column1 from table
else if @column  = 'column2' select column2 from table
...


3. дерзайте.


Совет интересный, жаль в таблице 695 полей, придется отбирать все возможные варианты и писать много строчек кода.
11 фев 21, 11:40    [22279399]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
Sergey Syrovatchenko
Member

Откуда:
Сообщений: 169
Касательно перебора полей можно это все нагенерить как бы... но в рамках функции это все равно не обернуть

DROP TABLE IF EXISTS dbo.tbl
GO

CREATE TABLE dbo.tbl (a INT, b INT, c INT, d INT)
GO

DROP TABLE IF EXISTS #t
SELECT [name] = ',' + [name]
INTO #t
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.tbl')

DECLARE @rn INT = @@rowcount

DROP TABLE IF EXISTS #variants

;WITH cte AS
(
    SELECT lvl = @rn, x = (
        SELECT [name]
        FROM #t
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

    UNION ALL

    SELECT t1.lvl - 1, REPLACE(t1.x, t2.[name], '')
    FROM cte t1
    CROSS APPLY #t t2
    WHERE t1.lvl > 1
)
SELECT DISTINCT x
INTO #variants
FROM cte

SELECT 'select ' + STUFF(x, 1, 1, '') + ' from dbo.tbl'
FROM #variants
11 фев 21, 15:14    [22279533]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
ol_chig
Member

Откуда:
Сообщений: 7
Sergey Syrovatchenko, тем не менее спасибо за пример, возможно где-то пригодится
11 фев 21, 15:54    [22279565]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3971
ol_chig
Sergey Syrovatchenko, тем не менее спасибо за пример, возможно где-то пригодится


в другой работе, например
11 фев 21, 16:01    [22279568]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
uaggster
Member

Откуда:
Сообщений: 954
Ролг Хупин,
НА другой...
11 фев 21, 19:43    [22279720]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
L_argo
Member

Откуда:
Сообщений: 1398
Совет интересный, жаль в таблице 695 полей
Был душевнобольной разработчик.
11 фев 21, 21:17    [22279752]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
vikkiv
Member

Откуда: EU
Сообщений: 2916
uaggster,

Наверное здесь приоритет на значимости немного другого синтаксиса.
Общество любителей литературы и поэзии чуть-чуть в другом месте.

L_argo,

Может опыта широты задач не хватает? Есть достаточно ситуаций когда такой подход вполне оправдан,
напр. у нас в первичном Stage из CSV и прочих бывает заливается и побольше полей..
11 фев 21, 21:52    [22279758]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8322
L_argo
Совет интересный, жаль в таблице 695 полей
Был душевнобольной разработчик.

Тема вообще бред.
Попытка скомпенсировать недостаток знаний бурным воображением. Отсюда "космические" решения.
11 фев 21, 22:39    [22279767]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
ol_chig
Member

Откуда:
Сообщений: 7
Владислав Колосов
L_argo
пропущено...
Был душевнобольной разработчик.

Тема вообще бред.
Попытка скомпенсировать недостаток знаний бурным воображением. Отсюда "космические" решения.

Таблица является справочным хранилищем, где есть поле, указывающее на вид аналитики. Ее проектированием занимался не один душевнобольной разработчик, а целая компания, которая неплохо продает свой продукт.
12 фев 21, 09:42    [22279875]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
tunknown
Member

Откуда:
Сообщений: 768
ol_chig
Динамические запросы вещь удобная, но их формирование и дальнейшая работа внутри функции нереализуема (или нет?)
Если пересоздавать каждый раз функцию с нужным текстом и "фиксированным" именем, то вопрос перейдёт от выполнения динмического sql внутри UDF к "фиксации" имени. Но придётся разбираться с одновременным выполнением и кешированием/перекомпиляцией.
12 фев 21, 11:37    [22279929]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
invm
Member

Откуда: Москва
Сообщений: 9634
ol_chig
Ее проектированием занимался не один душевнобольной разработчик, а целая компания, которая неплохо продает свой продукт.
Продаваемость очень мало коррелирует с вменяемостью разработчиков.

Нужно иметь либо серьезные основания, либо низкую квалификацию, чтобы делать таблицу с таким количеством столбцов.
12 фев 21, 12:13    [22279948]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
.Евгений
Member

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

ваш разработчик сделал удобно для себя (я не уверен в этом), но неудобно для вас - для потребителя данных. Задайте ему вопрос, каким он видит эффективное обращение к его таблице.
12 фев 21, 12:29    [22279958]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
vikkiv
Member

Откуда: EU
Сообщений: 2916
.Евгений,

Не сильно разумно сваливать всё на кого-то, есть достаточно вполне приемлемых решений с собственными генераторами запросов на основе метаданных+модель и со своей встроенной специфичной логикой в зависимости от требований, у них свой фэн-шуй и приоритеты отличные от его понимания разработчиками баз данных. Напр. у нас рисковые не агрегируемые метрики за 700 полей из внешних (дорогих) систем могут вполне легко улететь..
12 фев 21, 13:11    [22279987]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
.Евгений
Member

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

Я не говорю, что 695 полей делать неправильно, нельзя или глупо. Это отдельный вопрос, который обсуждать как минимум рано.
Я говорю о том, что провайдер данных (с моей точки зрения) должен не просто нагенерить их тем или иным способом, но представить их так, чтобы потребителю этих данных было удобно их использовать.

Когда потребитель данных обращается к третьей стороне для преобразования этих данных, то данные, очевидно, не представлены в удобном для него виде. Возможно, это задача ETL - если провайдер по каким-то причинам не может или не хочет дать к ним удобный доступ.
12 фев 21, 13:23    [22279994]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
HornetBlack
Member

Откуда: Питер
Сообщений: 65
ol_chig,

если работать с порядковым номером поля, то можно немного короче:
select choose(@num,column1,column2,...) from table
14 фев 21, 01:24    [22280682]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
ol_chig
Member

Откуда:
Сообщений: 7
HornetBlack
ol_chig,

если работать с порядковым номером поля, то можно немного короче:
select choose(@num,column1,column2,...) from table


Данная конструкция позволит по номеру определить имя поле, которое мне и так известно.
Может я не так понял и из этого нужно вырулить куда-то?
18 фев 21, 12:47    [22282731]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
Владислав Колосов
Member

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

это же не динамический запрос.
18 фев 21, 14:11    [22282809]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
msLex
Member

Откуда:
Сообщений: 8716
HornetBlack
ol_chig,

если работать с порядковым номером поля, то можно немного короче:
select choose(@num,column1,column2,...) from table



нельзя так делать

тип данных, возвращаемый choose, не зависит от первого параметра

https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-choose-transact-sql?view=sql-server-ver15

Return Types
Returns the data type with the highest precedence from the set of types passed to the function


create table #t(c varchar(100), d date)


insert #t(c, d)
select 'blalba', getdate()


select choose(1, c, d) from #t
18 фев 21, 14:18    [22282817]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
ol_chig
Member

Откуда:
Сообщений: 7
msLex
HornetBlack
ol_chig,

[/src]

хотя если сделать так
select choose(2, cast(c as varchar(max)), cast(d as varchar(max))) from #t

то что-то можно будет сделать, нужно учесть формат каждого поля и написать правильный конверт. Опять же костыль обретает вселенский масштаб в скобках choose и если каждый из 700 полей переводить в varchar, то на сколько быстро это все будет работать?

Сообщение было отредактировано: 18 фев 21, 14:36
18 фев 21, 14:42    [22282840]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
msLex
Member

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


тогда уж лучше sql_variant, хотя бы сохранятся данные об исходном типе

create table #t(c varchar(100), d date)


insert #t(c, d)
select 'blalba', getdate()

; with a as (
select	
	c = choose(1, cast(c as sql_variant), cast(d as sql_variant)) 
	, d = choose(2, cast(c as sql_variant), cast(d as sql_variant)) 
from #t
)
select 
	c
	, d
	, sql_variant_property (c, 'BaseType')
	, sql_variant_property (d, 'BaseType')
from a

drop table #t
18 фев 21, 14:48    [22282852]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3016
ol_chig
переводить в varchar, то на сколько быстро это все будет работать?

еще дико фиговые варианты
1. всё в xml и потом отдуда выбрать
Declare @column_name nvarchar(10) = 'i';

Select 
	i.value('*[local-name() = sql:variable("@column_name")][1]', 'nvarchar(100)')
from
	@xml.nodes('/row') x(i)


если еще одним параметром передовать тип столбца, то можно исподьзуя iif прописать для каждого типа возврат в необходимом типе

2. небольшое изменение пункта 1.: можно колонки привести к типу sparse добавить sparse column set и в итоге будет готовый xml, из которго можно будет таке делать выборку
19 фев 21, 11:57    [22283357]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8820
HandKot
ol_chig
переводить в varchar, то на сколько быстро это все будет работать?

еще дико фиговые варианты
1. всё в xml и потом отдуда выбрать
Declare @column_name nvarchar(10) = 'i';

Select 
	i.value('*[local-name() = sql:variable("@column_name")][1]', 'nvarchar(100)')
from
	@xml.nodes('/row') x(i)


если еще одним параметром передовать тип столбца, то можно исподьзуя iif прописать для каждого типа возврат в необходимом типе

2. небольшое изменение пункта 1.: можно колонки привести к типу sparse добавить sparse column set и в итоге будет готовый xml, из которго можно будет таке делать выборку


Гы... Я в проекте WEB-сервиса на стороне заказчика так передавал разнотипные данные через JSON . 6л@ буду, но туда-сюда гонять JSON оказалось быстро и логировать вызов/возврат наглядно - шикарно вышло. А вот временные таблицы не прокатили. И табличные переменные - тоже...
19 фев 21, 13:40    [22283473]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива динамическому запросу внутри функции?  [new]
msLex
Member

Откуда:
Сообщений: 8716
SIMPLicity_
HandKot
пропущено...

еще дико фиговые варианты
1. всё в xml и потом отдуда выбрать
Declare @column_name nvarchar(10) = 'i';

Select 
	i.value('*[local-name() = sql:variable("@column_name")][1]', 'nvarchar(100)')
from
	@xml.nodes('/row') x(i)



если еще одним параметром передовать тип столбца, то можно исподьзуя iif прописать для каждого типа возврат в необходимом типе

2. небольшое изменение пункта 1.: можно колонки привести к типу sparse добавить sparse column set и в итоге будет готовый xml, из которго можно будет таке делать выборку


Гы... Я в проекте WEB-сервиса на стороне заказчика так передавал разнотипные данные через JSON . 6л@ буду, но туда-сюда гонять JSON оказалось быстро и логировать вызов/возврат наглядно - шикарно вышло. А вот временные таблицы не прокатили. И табличные переменные - тоже...

Это если вам нужно передать пару десятков - сотен записей
А если, например, насколько сотен тысяч записей?
19 фев 21, 13:45    [22283480]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft SQL Server Ответить