ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как передать в хранимку список значений, чтобы не перебирать каждое?
8 сообщений из 8, страница 1 из 1
Как передать в хранимку список значений, чтобы не перебирать каждое?
    #39909584
palladin600
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
palladin600
Участник
Добрый день!

Застрял на одном вопросе, подскажите, есть табличка:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
declare @Pupils table(ClassID int, [Name] nvarchar(10))
insert into @Pupils(ClassID, [Name])
select 1, N'Ваня' union all
select 1, N'Петя' union all
select 1, N'Жора' union all
select 4, N'Даша' union all
select 4, N'Маша' union all
select 5, N'Таня'



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

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

Вызов хранимки примерной такой: EXEC dbo.PupilsInsert @ClassID = 1, @Pupilsnames = 'Ваня,Петя,Жора,Маша';
и, теперь, нужно отобрать всех учеников из первого класса и добавить только тех, кто отсутствует (т.е. добавить нужно только Машу) из переданного параметра.
Накидываю хранимку, да только тщетно всё.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
CREATE PROCEDURE [dbo].[PupilsInsert]
    ( @ClassID int)
    , @PupilsNames nvarchar(max))
AS BEGIN
    MERGE [dbo].[@Pupils] [Target]
    USING (VALUES (@ClassID, @PupilsNames)) [Source]([ClassID], [Name])
         ON [Target].[ClassID] = [Source].[ClassID] AND [Target].[Name] = [Source].[Name]
     WHEN NOT MATCHED THEN
        INSERT ([ClassID], [Name])
        VALUES ([Source].[ClassID], [Source].[Name]);
END



С уважением.
...
Рейтинг: 0 / 0
29.12.2019, 14:53
| Ответить | Цитировать | Написать  
Как передать в хранимку список значений, чтобы не перебирать каждое?
    #39909601
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
palladin600
но я не могу сообразить как передать сразу пачку учеников
Варианты:
1) Передать табличку как параметр (см. табличные параметры )
2) Сделать внутри таблицу-переменную, в неё вставить (распарсить) ваш параметр @PupilsNames nvarchar(max), на этом форуме можно найти примеры.
3) Передать список в виде XML, использовать функции работы с XML для получения таблицы, которую можно либо вставить в ту же таблицу-переменную, либо использовать напрямую в запросе.
...
Рейтинг: 0 / 0
29.12.2019, 15:59
| Ответить | Цитировать | Написать  
Как передать в хранимку список значений, чтобы не перебирать каждое?
    #39909616
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
alexeyvg
palladin600
но я не могу сообразить как передать сразу пачку учеников
Варианты:
1) Передать табличку как параметр (см. табличные параметры )
2) Сделать внутри таблицу-переменную, в неё вставить (распарсить) ваш параметр @PupilsNames nvarchar(max), на этом форуме можно найти примеры.
3) Передать список в виде XML, использовать функции работы с XML для получения таблицы, которую можно либо вставить в ту же таблицу-переменную, либо использовать напрямую в запросе.

Эээх.
Самого простого и правильного не сказал.
1. Тупо вставить значения во временную табличку.
2. Обработать эту времянку в процедуре.
...
Рейтинг: 0 / 0
29.12.2019, 17:06
| Ответить | Цитировать | Написать  
Как передать в хранимку список значений, чтобы не перебирать каждое?
    #39909631
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
aleks222
alexeyvg
пропущено...
Варианты:
1) Передать табличку как параметр (см. табличные параметры )
2) Сделать внутри таблицу-переменную, в неё вставить (распарсить) ваш параметр @PupilsNames nvarchar(max), на этом форуме можно найти примеры.
3) Передать список в виде XML, использовать функции работы с XML для получения таблицы, которую можно либо вставить в ту же таблицу-переменную, либо использовать напрямую в запросе.

Эээх.
Самого простого и правильного не сказал.
1. Тупо вставить значения во временную табличку.
2. Обработать эту времянку в процедуре.
Ой, да, есть и такой способ, надо было упомянуть.
Но всё таки лучше учиться другим, а то с этой временной таблицей будут перекомпиляции, при массовых вызовах будет просадка производительности.
...
Рейтинг: 0 / 0
29.12.2019, 18:48
| Ответить | Цитировать | Написать  
Как передать в хранимку список значений, чтобы не перебирать каждое?
    #39909743
entrypoint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
entrypoint
Гость
palladin600,

Создание объектов

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
-- Таблица
CREATE TABLE dbo.Pupils
             (ClassID INT NOT NULL
            , [Name]  NVARCHAR(10) NOT NULL
                                   PRIMARY KEY CLUSTERED (ClassID, [Name])
             );
GO 

-- Табличный тип
CREATE TYPE dbo.t AS TABLE
                           (ClassID INT NOT NULL
                          , [Name]  NVARCHAR(10) NOT NULL
                            PRIMARY KEY CLUSTERED(ClassID, [Name])
                           );
GO

-- Хранимая процедура
CREATE PROCEDURE [dbo].[PupilsInsert] @t dbo.t READONLY
AS
     MERGE [dbo].[Pupils] [Target]
     USING @t AS [Source]
     ON [Target].[ClassID] = [Source].[ClassID]
        AND [Target].[Name] = [Source].[Name]
         WHEN NOT MATCHED BY TARGET
           THEN
           INSERT(
                  [ClassID]
                , [Name])
           VALUES( 
                  [Source].[ClassID]
                , [Source].[Name]
                  )
         WHEN NOT MATCHED BY SOURCE
           THEN DELETE;
GO



Выполнение примера

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
DECLARE @t dbo.t;

INSERT INTO @t(
       ClassID
     , [Name]
             )
SELECT 
       1
     , N'Ваня'
UNION
SELECT 
       1
     , N'Петя'
UNION
SELECT 
       1
     , N'Жора'
UNION
SELECT 
       4
     , N'Даша'
UNION
SELECT 
       4
     , N'Маша'
UNION
SELECT 
       5
     , N'Таня';

EXECUTE [dbo].[PupilsInsert] 
        @t = @t;

SELECT 
       *
FROM 
     dbo.Pupils;

DELETE FROM @t; 

INSERT INTO @t(
       ClassID
     , [Name]
             )
SELECT 
       1
     , N'Ваня'
UNION
SELECT 
       1
     , N'Марк'
UNION
SELECT 
       5
     , N'Марк';

EXECUTE [dbo].[PupilsInsert] 
        @t = @t;

SELECT 
       *
FROM 
     dbo.Pupils;



Будет работать правильно только для полного обновления таблицы,
для частичного нужно мержить через CTE
...
Рейтинг: 0 / 0
30.12.2019, 10:28
| Ответить | Цитировать | Написать  
Как передать в хранимку список значений, чтобы не перебирать каждое?
    #39909822
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
alexeyvg
aleks222
пропущено...

Эээх.
Самого простого и правильного не сказал.
1. Тупо вставить значения во временную табличку.
2. Обработать эту времянку в процедуре.
Ой, да, есть и такой способ, надо было упомянуть.
Но всё таки лучше учиться другим, а то с этой временной таблицей будут перекомпиляции, при массовых вызовах будет просадка производительности.


Ога, парсинг хмл - он сам делается, без затрат ресурсов.
...
Рейтинг: 0 / 0
30.12.2019, 13:16
| Ответить | Цитировать | Написать  
Как передать в хранимку список значений, чтобы не перебирать каждое?
    #39909827
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
aleks222,

нет идеального решения в такой ситуации. Все имеют недостатки.
...
Рейтинг: 0 / 0
30.12.2019, 13:25
| Ответить | Цитировать | Написать  
Как передать в хранимку список значений, чтобы не перебирать каждое?
    #39909950
palladin600
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
palladin600
Участник
Спасибо ребята, entrypoint спасибо за пример.

Ещё вот от одного человека получил идею в виде JSON:
Код: sql
1.
2.
3.
DECLARE
  @ClassID int = 1,
  @PupilsNames varchar(MAX) = '["Ваня", "Петя", "Жора", "Маша"]' -- JSON array



Код: sql
1.
2.
3.
-- а значения из JSON в ввиде строк вернёт запрос:
SELECT *
FROM OPENJSON(@PupilsNames)


Код: plaintext
1.
2.
3.
0	Ваня	1
1	Петя	1
2	Жора	1
3	Маша	1
...
Рейтинг: 0 / 0
30.12.2019, 16:29
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как передать в хранимку список значений, чтобы не перебирать каждое?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=39909616&tid=1686693]:
 0ms
get settings:
 11ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 37ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 39ms
get tp. blocked users:
 1ms
others: 331ms
total:  450ms
созд. / загр.: 450ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]