Как выполнить хранимую процедуру один раз для каждой строки, возвращаемой запросом?

Ответ 1

используйте курсор

ДОБАВЛЕНИЕ: [Пример курсора MS SQL]

declare @field1 int
declare @field2 int
declare cur CURSOR LOCAL for
    select field1, field2 from sometable where someotherfield is null

open cur

fetch next from cur into @field1, @field2

while @@FETCH_STATUS = 0 BEGIN

    --execute your sproc on each row
    exec uspYourSproc @field1, @field2

    fetch next from cur into @field1, @field2
END

close cur
deallocate cur

в MS SQL, здесь пример статьи

обратите внимание, что курсоры работают медленнее, чем операции на основе набора, но быстрее, чем ручные while-loops; подробнее в этом вопросе SO

ADDENDUM 2: если вы будете обрабатывать больше, чем несколько записей, сначала вставьте их в таблицу temp и запустите курсор над временной таблицей; это предотвратит перерастание SQL в таблицы-блокировки и ускорит работу

ДОБАВЛЕНИЕ 3: и, конечно, если вы можете встроить то, что ваша хранимая процедура выполняет с каждым идентификатором пользователя, и запустить все это как единую инструкцию по обновлению SQL, это было бы оптимальным

Ответ 2

попытайтесь изменить свой метод, если вам нужно выполнить цикл!

в родительской хранимой процедуре, создайте таблицу #temp, которая содержит данные, которые необходимо обработать. Вызовите дочернюю хранимую процедуру, таблица #temp будет видна, и вы можете обработать ее, надеюсь, работать со всем набором данных и без курсора или цикла.

это действительно зависит от того, что делает эта хранимая процедура. Если вы ОБНОВЛЯЕТСЯ, вы можете "обновить с" соединение в таблице #temp и выполнить всю работу в одном из операторов без цикла. То же самое можно сделать для INSERT и DELETE. Если вам нужно сделать несколько обновлений с IF, вы можете преобразовать их в несколько UPDATE FROM с помощью таблицы #temp и использовать операторы CASE или WHERE.

При работе в базе данных старайтесь потерять мышление цикла, это реальный дренаж производительности, приведет к блокировке/блокировке и замедлению обработки. Если вы зацикливаетесь повсюду, ваша система не будет масштабироваться очень хорошо, и будет очень сложно ускорить, когда пользователи начнут жаловаться на медленные обновления.

Опубликуйте содержимое этой процедуры, которую вы хотите вызывать в цикле, и я поставил 9 из 10 раз, вы можете написать ее для работы с набором строк.

Ответ 3

Что-то вроде этих подстановок будет необходимо для ваших таблиц и имен полей.

Declare @TableUsers Table (User_ID, MyRowCount Int Identity(1,1)
Declare @i Int, @MaxI Int, @UserID nVarchar(50)

Insert into @TableUser
Select User_ID
From Users 
Where (My Criteria)
Select @MaxI = @@RowCount, @i = 1

While @i <= @MaxI
Begin
Select @UserID = UserID from @TableUsers Where MyRowCount = @i
Exec prMyStoredProc @UserID
Select

 @i = @i + 1, @UserID = null
End

Ответ 4

Вы можете сделать это с помощью динамического запроса.

declare @cadena varchar(max) = ''
select @cadena = @cadena + 'exec spAPI ' + ltrim(id) + ';'
from sysobjects;
exec(@cadena);

Ответ 5

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

SELECT udfMyFunction(user_id), someOtherField, etc FROM MyTable WHERE WhateverCondition

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

Смотрите http://www.sqlteam.com/article/user-defined-functions, чтобы узнать больше

Я согласен, что курсоров действительно следует избегать, где это возможно. И это обычно возможно!

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

Ответ 6

Используйте переменную таблицы или временную таблицу.

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

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

Создайте переменную таблицы следующим образом (если вы работаете с большим количеством данных или у вас недостаточно памяти, используйте вместо этого временную таблицу):

DECLARE @menus AS TABLE (
    id INT IDENTITY(1,1),
    parent NVARCHAR(128),
    child NVARCHAR(128));

id важен.

Замените parent и child некоторыми хорошими данными, например, соответствующими идентификаторами или целым набором данных, с которыми нужно работать.

Вставьте данные в таблицу, например:

INSERT INTO @menus (parent, child) 
  VALUES ('Some name',  'Child name');
...
INSERT INTO @menus (parent,child) 
  VALUES ('Some other name', 'Some other child name');

Объявите некоторые переменные:

DECLARE @id INT = 1;
DECLARE @parentName NVARCHAR(128);
DECLARE @childName NVARCHAR(128);

И наконец, создайте цикл while для данных в таблице:

WHILE @id IS NOT NULL
BEGIN
    SELECT @parentName = parent,
           @childName = child 
        FROM @menus WHERE id = @id;

    EXEC myProcedure @[email protected], @[email protected];

    SELECT @id = MIN(id) FROM @menus WHERE id > @id;
END

Первый выбор извлекает данные из временной таблицы. Второй выбор обновляет @id. MIN возвращает ноль, если строки не были выбраны.

Альтернативный подход состоит в том, чтобы зациклить, пока в таблице есть строки, SELECT TOP 1 и удалить выбранную строку из временной таблицы:

WHILE EXISTS(SELECT 1 FROM @menuIDs) 
BEGIN
    SELECT TOP 1 @menuID = menuID FROM @menuIDs;

    EXEC myProcedure @[email protected];

    DELETE FROM @menuIDs WHERE menuID = @menuID;
END;

Ответ 7

Мне нравится динамический способ запроса Дэйва Ринкона, поскольку он не использует курсоры и является небольшим и легким. Спасибо Дэйву за обмен.

Но для моих потребностей в Azure SQL и с "отличным" в запросе мне пришлось изменить код следующим образом:

Declare @SQL nvarchar(max);
-- Set SQL Variable
-- Prepare exec command for each distinctive tenantid found in Machines 
SELECT @SQL = (Select distinct 'exec dbo.sp_S2_Laser_to_cache ' + 
              convert(varchar(8),tenantid) + ';' 
              from Dim_Machine
              where iscurrent = 1
              FOR XML PATH(''))

--for debugging print the sql 
print @SQL;

--execute the generated sql script
exec sp_executesql @SQL;

Я надеюсь, что это поможет кому-то...