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

Откуда:
Сообщений: 109
Коллеги, добрый день!

В хранимой процедуре необходимо понимать, какие параметры не были переданы, а каким параметрам передано значение NULL (т.е. определить смысл значения NULL). Это необходимо для реализации логики обновления полей (если не передано значение - не трогаем, передано - обновляем). У кого-нибудь есть пример шаблона хранимой процедуры для решения данной задачи?

Вижу 2 варианта.
1) Ввести входной параметр маски реально переданных параметров (т.е. вызывающая сторона уточняет перечень передаваемых параметров);
2) Определить специальные значения входных параметров, которые будут указывать, что передано пустое значение (т.е. default значение null означает отсутствие передачи параметра, а пустая строка, например для varchar, означает необходимость сбросить значение поля в БД в NULL).

Спасибо)
12 ноя 21, 13:38    [22395055]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 663
Tketano,

Вариант 3. Не выворачивать мозги. Передавать всегда и всё и обновлять тем, что передано.
12 ноя 21, 13:54    [22395067]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1908
Tketano,

create or alter procedure [usp_update]
         @id int,
         @val1 varchar(255) = null,
         @val2 varchar(255) = null
as
update [dbo].[tbl]
   set [val1] = isnull(@val1, [val1]),
       [val2] = isnull(@val2, [val2])
where [id] = @id;


?
12 ноя 21, 14:23    [22395106]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
Tketano
Member

Откуда:
Сообщений: 109
Guf,
автор
Вариант 3. Не выворачивать мозги. Передавать всегда и всё и обновлять тем, что передано.

Есть сущности, содержащие более 100 столбцов. Объявлять овер 100 переменных и начитывать их перед EXEC - ну такое себе.
И, часто, надо обновить лишь небольшую часть столбцов. Понятно, что бизнес-логика должна быть разбита по разным ХП, но не всегда и не везде это получается. Прямые UPDATE на таблицу не рассматриваются, т.к. пролетают мимо важных контролей внутри ХП.
12 ноя 21, 14:43    [22395122]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
Tketano
Member

Откуда:
Сообщений: 109
felix_ff
Tketano,

create or alter procedure [usp_update]
         @id int,
         @val1 varchar(255) = null,
         @val2 varchar(255) = null
as
update [dbo].[tbl]
   set [val1] = isnull(@val1, [val1]),
       [val2] = isnull(@val2, [val2])
where [id] = @id;


?


Это работает до тех пор, пока вам не надо "пропихнуть" NULL как реальное значение. Т.е. вы никогда не сможете обnullить значение в БД.
12 ноя 21, 14:45    [22395126]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
andy st
Member

Откуда:
Сообщений: 899
Tketano,
делайте тогда процедуру, у которой на входе будет названия поля и значение (или несколько в зависимости от типов данных, или с преобразованием в зависимости от типа поля целевой таблицы) и внутри процедуры пилите динамический sql на изменение конкретного поля или портянку на 100500 строк с условиями/case when
12 ноя 21, 16:36    [22395196]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
andy st
Member

Откуда:
Сообщений: 899
А есть еще вариант реализации, поражающий своим безграничным ужасом - формировать update нужного поля (или полей) на клиенте.
12 ноя 21, 16:42    [22395201]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
Владислав Колосов
Member

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

это кручу-верчу бред, сделайте все параметры обязательными для заполнения. За угадыванием надо к гадалке ходить, а не программы писать.
12 ноя 21, 17:52    [22395253]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
PaulYoung
Member

Откуда: Москва
Сообщений: 2576
andy st
Tketano,
делайте тогда процедуру, у которой на входе будет названия поля и значение (или несколько в зависимости от типов данных, или с преобразованием в зависимости от типа поля целевой таблицы) и внутри процедуры пилите динамический sql на изменение конкретного поля или портянку на 100500 строк с условиями/case when
Не дай Бог такое унаследовать и поддерживать. Боль.
12 ноя 21, 18:03    [22395258]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
andy st
Member

Откуда:
Сообщений: 899
PaulYoung
andy st
Tketano,
делайте тогда процедуру, у которой на входе будет названия поля и значение (или несколько в зависимости от типов данных, или с преобразованием в зависимости от типа поля целевой таблицы) и внутри процедуры пилите динамический sql на изменение конкретного поля или портянку на 100500 строк с условиями/case when
Не дай Бог такое унаследовать и поддерживать. Боль.

Человек хочет боль, человек получит боль
12 ноя 21, 18:05    [22395259]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
SERG1257
Member

Откуда:
Сообщений: 2931
Соглашаясь с тем что это боль

поддерживаю вариант 1 с маской полей (или несколькими масками если полей много)

Сообщение было отредактировано: 12 ноя 21, 18:22
12 ноя 21, 18:20    [22395267]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
andy st
Member

Откуда:
Сообщений: 899
Вы еще определитесь с тем, кто же по планам будет больше страдать в процессе разработки взаимодействия клиентского ПО с базой - разработчик клиентского софта или разработчик субд. Ну и к этому прикрутите их квалификацию.
Если разраб клиента "эпичен", а разработчик субд из начинающих - пусть колбасят ad-hoc на update на клиенте, не такая сложная задача. Это если контроли в хп можно на триггеры заменить
Если разраб клиента умеет подставлять параметры в процедуру, а разраб СУБД крут - любой вариант с сложной и поддерживаемой с болью логикой в процедуре, в том числе и вариант с параметрами "название поля" и "значение поля" - не факт что такой разработчик клиента осилит запилить маску полей для заполнения
Если оба достаточно компетентны - развлекайтесь как вам угодно, благо возможностей для реализации такого глума тьма

Сообщение было отредактировано: 12 ноя 21, 19:10
12 ноя 21, 19:06    [22395300]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1908
Tketano
felix_ff
Tketano,

create or alter procedure [usp_update]
         @id int,
         @val1 varchar(255) = null,
         @val2 varchar(255) = null
as
update [dbo].[tbl]
   set [val1] = isnull(@val1, [val1]),
       [val2] = isnull(@val2, [val2])
where [id] = @id;


?


Это работает до тех пор, пока вам не надо "пропихнуть" NULL как реальное значение. Т.е. вы никогда не сможете обnullить значение в БД.


ну окей, объявите у параметров явные дефолты и сверяйтесь с ними
create or alter procedure [usp_update]
         @id int,
         @val1 varchar(255) = '__default_va1',
         @val2 varchar(255) = '__default_val2'
as
update [dbo].[tbl]
   set [val1] = case when @val1 = '__default_val1' then [val1] else @val1 end,
       [val2] = case when @val2 = '__default_val2' then [val2] else @val2 end
where [id] = @id;


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

но имхо: по большей части вы занимаетесь ерундой, контроль правильности значений должен быть на стороне сервера приложений/клиентской части.
+

сейчас в меня полетят ссаные тряпки адептов реализации бизнес логики на стороне СУБД
12 ноя 21, 21:10    [22395356]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
andy st
Member

Откуда:
Сообщений: 899
felix_ff
нужно правда придумать такие дефолты которые в принципе никогда не встретятся в качестве реальных значений таблицы.

гуиды вполне проканают, для параноиков - пара гуидов
felix_ff

но имхо: по большей части вы занимаетесь ерундой, контроль правильности значений должен быть на стороне сервера приложений/клиентской части.
+

сейчас в меня полетят ссаные тряпки адептов реализации бизнес логики на стороне СУБД

лови первый :)
12 ноя 21, 21:20    [22395359]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
.Евгений
Member

Откуда:
Сообщений: 707
Принимать на входе XML/Json и формировать по нему динамический Update предлагали?
Если плевать на производительность, то может прокатить.
12 ноя 21, 21:36    [22395364]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
andy st
Member

Откуда:
Сообщений: 899
А потом тредстартер прочитает что ему тут насоветовали и скажет: "да ну нахрен, пойду в дворники"
12 ноя 21, 21:52    [22395373]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1908
.Евгений
Принимать на входе XML/Json и формировать по нему динамический Update предлагали?
Если плевать на производительность, то может прокатить.


хотите пострадать фигней? :)
+

CLR процедуры сохраняют свои default value параметров в sys.parameters.
если прямо сильно заморочиться можно нарисовать CLR обертку где дефолты будут явно тянуться при явном игноре входного параметра.
другой вопрос: что скажут остальные разрабы когда создатель любой "мегофичи" уволится, а им потребуется насладится поддержкой его "высокого полета мысли" :)
13 ноя 21, 01:43    [22395455]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
.Евгений
Member

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

трансляция XML в Update в общем не сложна, логика наглядна. Думаю, что поддерживать такое было бы менее противно, чем описанное вами.
13 ноя 21, 16:17    [22395597]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4420
Tketano
Guf,
автор
Вариант 3. Не выворачивать мозги. Передавать всегда и всё и обновлять тем, что передано.

Есть сущности, содержащие более 100 столбцов. Объявлять овер 100 переменных и начитывать их перед EXEC - ну такое себе.
И, часто, надо обновить лишь небольшую часть столбцов. Понятно, что бизнес-логика должна быть разбита по разным ХП, но не всегда и не везде это получается. Прямые UPDATE на таблицу не рассматриваются, т.к. пролетают мимо важных контролей внутри ХП.


Люди-то в основном злые, не стоит расчитывать на понимание
13 ноя 21, 20:05    [22395656]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
aleks222
Member

Откуда:
Сообщений: 1839
felix_ff
Tketano
пропущено...


Это работает до тех пор, пока вам не надо "пропихнуть" NULL как реальное значение. Т.е. вы никогда не сможете обnullить значение в БД.


ну окей, объявите у параметров явные дефолты и сверяйтесь с ними
create or alter procedure [usp_update]
         @id int,
         @val1 varchar(255) = '__default_va1',
         @val2 varchar(255) = '__default_val2'
as
update [dbo].[tbl]
   set [val1] = case when @val1 = '__default_val1' then [val1] else @val1 end,
       [val2] = case when @val2 = '__default_val2' then [val2] else @val2 end
where [id] = @id;


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

но имхо: по большей части вы занимаетесь ерундой, контроль правильности значений должен быть на стороне сервера приложений/клиентской части.
+

сейчас в меня полетят ссаные тряпки адептов реализации бизнес логики на стороне СУБД



create or alter procedure [usp_update]
         @id int,
         @val1 varchar(255) = null, @val1On bit = 0,
         @val2 varchar(255) = null, @val2On bit = 0
as


Универсальные лисапеды всегда громоздки...
14 ноя 21, 11:28    [22395774]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32167
Tketano
И, часто, надо обновить лишь небольшую часть столбцов. Понятно, что бизнес-логика должна быть разбита по разным ХП, но не всегда и не везде это получается. Прямые UPDATE на таблицу не рассматриваются, т.к. пролетают мимо важных контролей внутри ХП.
Разбить обновления на разные ХП можно всегда.
Или можно делать контроль в триггерах.
Про всё это уже написали, я уверен, что эти решения будут лучше, чем изощрённая логика со 100 флагами.
Проще для разработки, для расширения функционала, для сопровождения, и будут содержать меньше ошибок.
14 ноя 21, 14:03    [22395820]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32167
alexeyvg
Tketano
И, часто, надо обновить лишь небольшую часть столбцов. Понятно, что бизнес-логика должна быть разбита по разным ХП, но не всегда и не везде это получается. Прямые UPDATE на таблицу не рассматриваются, т.к. пролетают мимо важных контролей внутри ХП.
Разбить обновления на разные ХП можно всегда.
Или можно делать контроль в триггерах.
Про всё это уже написали, я уверен, что эти решения будут лучше, чем изощрённая логика со 100 флагами.
Проще для разработки, для расширения функционала, для сопровождения, и будут содержать меньше ошибок.
Я даже больше скажу.

Подход "сделать процедуры для изменения любого набора полей" противоречит вашему же подходу "обрабатывать данные в СУБД, предоставляя API клиентам (серверам приложений и др.)"
Вы хотите дать возможность делать ad-hoc запросы к базе, только через процедуры, что абсолютно лишено смысла как бизнес, так и технологического. Процедуры ради процедур. Вырождение идеи "СУБД предоставляет API для работы с данными".
14 ноя 21, 14:11    [22395825]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9155
но не всегда и не везде это получается


это не может получаться только при отсутствии желания это делать.
14 ноя 21, 21:46    [22395978]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
Tketano
Member

Откуда:
Сообщений: 109
Всем спасибо за мнения! Динамический SQL явно мимо; финальные контроли осуществляются на сервере. Может изначально плохо пояснил задачу. Смысл в том, что есть много входных точек для изменения данных (api), реализующих разные форматы и разный набор полей. Соответственно вопрос был в обновлении информации в базе данных (после приема входящего запроса).
Пока остановились на следующем варианте:
create or alter procedure [usp_update_some_rekv]
	@id	int,
	@val1	int		= null,	-- -1 = сбросить в null (значение -1 не допускается бизнес-логикой)
	@val2	varchar(255)	= null,	-- '' = сбросить в null (пустая строка не допускается бизнес-логикой)
	@val3	date		= null	-- '' = сбросить в null (1900 год не допускается бизнес-логикой)
as

select	@val1 = case when @val1 = -1 then null else isnull(@val1, val1) end,
	@val2 = case when @val2 = '' then null else isnull(@val2, val2) end,
	@val3 = case when @val3 = '' then null else isnull(@val3, val3) end
from	[dbo].[tbl]
where	[id] = @id;

-- базовая процедура для записи параметров "как есть"
exec dbo.usp_update
		@id	= @id,
		@val1	= @val1,
		@val2	= @val2,
		@val3	= @val3
go


Хотя итоговое решение не понравилось (вызов такой ХП выглядит "неестественным"). Пока будет использоваться исключительно локально (для решения конкретной задачи). Возможно, часть контролей будет перенесена + прямой UPDATE таблицы + прочая логика процедуры usp_update.
15 ноя 21, 12:17    [22396122]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон SP для определения переданных параметров  [new]
Konst_One
Member

Откуда:
Сообщений: 11677
@val3	date
...
@val3 = case when @val3 = ''
15 ноя 21, 12:49    [22396147]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить