Como executo um procedimento armazenado uma vez para cada linha retornada pela consulta?

Eu tenho um procedimento armazenado que altera os dados do usuário de uma determinada maneira. Eu passá-lo user_id e isso é coisa. Eu quero executar uma consulta em uma tabela e, em seguida, para cada user_id eu encontrar executar o procedimento armazenado uma vez nesse user_id

Como eu escreveria uma consulta para isso?

use um cursor

ADENDO: [Exemplo de cursor do 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 

no MS SQL, aqui está um artigo de exemplo

note que os cursores são mais lentos que as operações baseadas em set, mas mais rápido que os loops while manuais; mais detalhes nesta questão SO

ADENDO 2: se você estiver processando mais do que apenas alguns registros, puxe-os para uma tabela temporária primeiro e passe o cursor sobre a tabela temporária; isso evitará que o SQL seja escalado para bloqueios de tabela e acelere a operação

ADENDO 3: e, claro, se você puder inline o que quer que seu procedimento armazenado esteja fazendo com cada ID de usuário e executar tudo como uma única instrução SQL update, isso seria ótimo

tente mudar o seu método se você precisar fazer um loop!

dentro do procedimento armazenado pai, crie uma tabela #temp que contenha os dados que você precisa processar. Chame o procedimento armazenado filho, a tabela #temp ficará visível e você poderá processá-la, esperamos que trabalhe com todo o dataset e sem um cursor ou loop.

isso realmente depende do que esta criança procedimento armazenado está fazendo. Se você está atualizando, você pode “atualizar de” entrar na tabela #temp e fazer todo o trabalho em uma instrução sem um loop. O mesmo pode ser feito para INSERT e DELETEs. Se você precisar fazer várias atualizações com IFs, poderá convertê-las para vários UPDATE FROM com a tabela #temp e usar as instruções CASE ou WHERE.

Ao trabalhar em um database, tente perder a mentalidade de looping, é um dreno de desempenho real, causará bloqueio / bloqueio e retardar o processamento. Se você fizer um loop em todos os lugares, seu sistema não será dimensionado muito bem e será muito difícil acelerar quando os usuários começarem a reclamar sobre atualizações lentas.

Poste o conteúdo deste procedimento que você quer chamar em um loop, e eu aposto 9 de 10 vezes, você poderia escrever para trabalhar em um conjunto de linhas.

Algo parecido com essas substituições será necessário para suas tabelas e nomes de campo.

 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 

Isso não pode ser feito com uma function definida pelo usuário para replicar o que seu procedimento armazenado está fazendo?

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

onde udfMyFunction é uma function que você faz com o ID do usuário e faz o que você precisa fazer com ele.

Veja http://www.sqlteam.com/article/user-defined-functions para um pouco mais de fundo

Concordo que os cursores devem ser evitados sempre que possível. E geralmente é possível!

(claro, minha resposta pressupõe que você só está interessado em obter a saída do SP e que você não está alterando os dados reais. Eu acho “altera os dados do usuário de uma certa maneira” um pouco ambíguo da pergunta original, então pensei em oferecer isso como uma solução possível. Depende totalmente do que você está fazendo!)

Você pode fazer isso com uma consulta dinâmica.

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

Eu gosto do modo de consulta dinâmica de Dave Rincon como ele não usa cursores e é pequeno e fácil. Obrigado Dave por compartilhar.

Mas para minhas necessidades no SQL do Azure e com um “distinto” na consulta, tive que modificar o código da seguinte forma:

 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; 

Espero que isso ajude alguém…

Use uma variável de tabela ou uma tabela temporária.

Como foi mencionado antes, um cursor é um último recurso. Principalmente porque ele usa muitos resources, bloqueia problemas e pode ser um sinal de que você não está entendendo como usar o SQL corretamente.

(Nota: Uma vez eu encontrei uma solução que usava cursores para atualizar linhas em uma tabela. Depois de algum escrutínio, a coisa toda poderia ser substituída por um único comando UPDATE.)

No entanto, nesse caso, onde um procedimento armazenado deve ser executado, um único comando SQL não funcionará.

Crie uma variável de tabela como essa (se você estiver trabalhando com muitos dados ou estiver com pouca memory, use uma tabela temporária ):

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

O id é importante.

Substitua parent e child por alguns bons dados, por exemplo, identificadores relevantes ou todo o dataset a serem operados.

Inserir dados na tabela, por exemplo:

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

Declarar algumas variables:

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

E finalmente, crie um loop while sobre os dados na tabela:

 WHILE @id IS NOT NULL BEGIN SELECT @parentName = parent, @childName = child FROM @menus WHERE id = @id; EXEC myProcedure @parent=@parentName, @child=@childName; SELECT @id = MIN(id) FROM @menus WHERE id > @id; END 

A primeira seleção busca dados da tabela temporária. A segunda seleção atualiza o @id. MIN retorna null se nenhuma linha foi selecionada.

Uma abordagem alternativa é fazer um loop enquanto a tabela possui linhas, SELECT TOP 1 e remover a linha selecionada da tabela temporária.

Update : Como se constata, eu precisava de uma iteração onde eu apague também, então aqui está:

 WHILE EXISTS(SELECT 1 FROM @menuIDs) BEGIN SELECT TOP 1 @menuID = menuID FROM @menuIDs; EXEC myProcedure @menuID=@menuID; DELETE FROM @menuIDs WHERE menuID = @menuID; END;