Procedimento armazenado de chamada SQL para cada linha sem usar um cursor

Como se pode chamar um procedimento armazenado para cada linha em uma tabela, onde as colunas de uma linha são parâmetros de input para o sp sem usar um Cursor?

De um modo geral, sempre procuro uma abordagem baseada em conjuntos (às vezes, às custas de alterar o esquema).

No entanto, este trecho tem o seu lugar ..

-- Declare & init (2008 syntax) DECLARE @CustomerID INT = 0 -- Iterate over all customers WHILE (1 = 1) BEGIN -- Get next customerId SELECT TOP 1 @CustomerID = CustomerID FROM Sales.Customer WHERE CustomerID > @CustomerId ORDER BY CustomerID -- Exit loop if no more customers IF @@ROWCOUNT = 0 BREAK; -- call your sproc EXEC dbo.YOURSPROC @CustomerId END 

Você poderia fazer algo assim: pedir sua tabela, por exemplo, CustomerID (usando a tabela de exemplo AdventureWorks Sales.Customer ) e iterar sobre esses clientes usando um loop WHILE:

 -- define the last customer ID handled DECLARE @LastCustomerID INT SET @LastCustomerID = 0 -- define the customer ID to be handled now DECLARE @CustomerIDToHandle INT -- select the next customer to handle SELECT TOP 1 @CustomerIDToHandle = CustomerID FROM Sales.Customer WHERE CustomerID > @LastCustomerID ORDER BY CustomerID -- as long as we have customers...... WHILE @CustomerIDToHandle IS NOT NULL BEGIN -- call your sproc -- set the last customer handled to the one we just handled SET @LastCustomerID = @CustomerIDToHandle SET @CustomerIDToHandle = NULL -- select the next customer to handle SELECT TOP 1 @CustomerIDToHandle = CustomerID FROM Sales.Customer WHERE CustomerID > @LastCustomerID ORDER BY CustomerID END 

Isso deve funcionar com qualquer tabela, desde que você possa definir algum tipo de ORDER BY em alguma coluna.

 DECLARE @SQL varchar(max)='' -- MyTable has fields fld1 & fld2 Select @SQL = @SQL + 'exec myproc ' + convert(varchar(10),fld1) + ',' + convert(varchar(10),fld2) + ';' From MyTable EXEC (@SQL) 

Ok, então eu nunca colocaria esse código em produção, mas ele satisfaz seus requisitos.

A resposta de Marc é boa (eu comentaria se pudesse descobrir como!)
Apenas pensei em apontar que talvez seja melhor alterar o loop para que o SELECT exista apenas uma vez (em um caso real em que precisei fazer isso, o SELECT era bastante complexo e escrevê-lo duas vezes era um problema de manutenção arriscado) .

 -- define the last customer ID handled DECLARE @LastCustomerID INT SET @LastCustomerID = 0 -- define the customer ID to be handled now DECLARE @CustomerIDToHandle INT SET @CustomerIDToHandle = 1 -- as long as we have customers...... WHILE @LastCustomerID <> @CustomerIDToHandle BEGIN SET @LastCustomerId = @CustomerIDToHandle -- select the next customer to handle SELECT TOP 1 @CustomerIDToHandle = CustomerID FROM Sales.Customer WHERE CustomerID > @LastCustomerId ORDER BY CustomerID IF @CustomerIDToHandle <> @LastCustomerID BEGIN -- call your sproc END END 

Se você puder transformar o procedimento armazenado em uma function que retorna uma tabela, poderá usar a aplicação cruzada.

Por exemplo, digamos que você tenha uma tabela de clientes e deseje calcular a sum de seus pedidos, crie uma function que receba um CustomerID e retorne a sum.

E você poderia fazer isso:

 SELECT CustomerID, CustomerSum.Total FROM Customers CROSS APPLY ufn_ComputeCustomerTotal(Customers.CustomerID) AS CustomerSum 

Onde a function seria semelhante:

 CREATE FUNCTION ComputeCustomerTotal ( @CustomerID INT ) RETURNS TABLE AS RETURN ( SELECT SUM(CustomerOrder.Amount) AS Total FROM CustomerOrder WHERE CustomerID = @CustomerID ) 

Obviamente, o exemplo acima poderia ser feito sem uma function definida pelo usuário em uma única consulta.

A desvantagem é que as funções são muito limitadas – muitas das características de um procedimento armazenado não estão disponíveis em uma function definida pelo usuário, e a conversão de um procedimento armazenado para uma function nem sempre funciona.

Para o SQL Server 2005 em diante, você pode fazer isso com CROSS APPLY e uma function com valor de tabela.

  • Usando CROSS APPLY no SQL Server 2005

Apenas para esclarecer, estou me referindo aos casos em que o procedimento armazenado pode ser convertido em uma function com valor de tabela.

Eu usaria a resposta aceita, mas outra possibilidade é usar uma variável de tabela para manter um conjunto numerado de valores (neste caso apenas o campo ID de uma tabela) e percorrê-los por Row Number com um JOIN na tabela para recuperar o que você precisa para a ação dentro do loop.

 DECLARE @RowCnt int; SET @RowCnt = 0 -- Loop Counter -- Use a table variable to hold numbered rows containg MyTable's ID values DECLARE @tblLoop TABLE (RowNum int IDENTITY (1, 1) Primary key NOT NULL, ID INT ) INSERT INTO @tblLoop (ID) SELECT ID FROM MyTable -- Vars to use within the loop DECLARE @Code NVarChar(10); DECLARE @Name NVarChar(100); WHILE @RowCnt < (SELECT COUNT(RowNum) FROM @tblLoop) BEGIN SET @RowCnt = @RowCnt + 1 -- Do what you want here with the data stored in tblLoop for the given RowNum SELECT @Code=Code, @Name=LongName FROM MyTable INNER JOIN @tblLoop tL on MyTable.ID=tL.ID WHERE tl.RowNum=@RowCnt PRINT Convert(NVarChar(10),@RowCnt) +' '+ @Code +' '+ @Name END 

Se você não sabe o que usar um cursor eu acho que você terá que fazê-lo externamente (pegue a tabela, e então corra para cada afirmação e cada vez chame o sp) É o mesmo que usar um cursor, mas somente fora SQL Por que você não usa um cursor?

Esta é uma variação da solução n3rds acima. Nenhuma sorting usando ORDER BY é necessária, pois MIN () é usado.

Lembre-se que CustomerID (ou qualquer outra coluna numérica que você usa para o progresso) deve ter uma restrição exclusiva. Além disso, para torná-lo o mais rápido possível, o CustomerID deve ser indexado.

 -- Declare & init DECLARE @CustomerID INT = (SELECT MIN(CustomerID) FROM Sales.Customer); -- First ID DECLARE @Data1 VARCHAR(200); DECLARE @Data2 VARCHAR(200); -- Iterate over all customers WHILE @CustomerID IS NOT NULL BEGIN -- Get data based on ID SELECT @Data1 = Data1, @Data2 = Data2 FROM Sales.Customer WHERE [ID] = @CustomerID ; -- call your sproc EXEC dbo.YOURSPROC @Data1, @Data2 -- Get next customerId SELECT @CustomerID = MIN(CustomerID) FROM Sales.Customer WHERE CustomerID > @CustomerId END 

Eu uso essa abordagem em alguns varchars que preciso examinar, colocando-os em uma tabela temporária primeiro, para dar a eles uma ID.

Eu costumo fazer isso quando são algumas linhas:

  1. Selecione todos os parâmetros de sproc em um dataset com o SQL Management Studio
  2. Clique com o botão direito -> Copiar
  3. Cole no Excel
  4. Crie instruções sql de linha única com uma fórmula como ‘= “EXEC schema.mysproc @ param =” & A2’ em uma nova coluna do excel. (Onde A2 é sua coluna do excel contendo o parâmetro)
  5. Copie a lista de instruções do excel em uma nova consulta no SQL Management Studio e execute.
  6. Feito.

(Em conjuntos de dados maiores eu usaria uma das soluções mencionadas acima).

DELIMITER //

 CREATE PROCEDURE setFakeUsers (OUT output VARCHAR(100)) BEGIN -- define the last customer ID handled DECLARE LastGameID INT; DECLARE CurrentGameID INT; DECLARE userID INT; SET @LastGameID = 0; -- define the customer ID to be handled now SET @userID = 0; -- select the next game to handle SELECT @CurrentGameID = id FROM online_games WHERE id > LastGameID ORDER BY id LIMIT 0,1; -- as long as we have customers...... WHILE (@CurrentGameID IS NOT NULL) DO -- call your sproc -- set the last customer handled to the one we just handled SET @LastGameID = @CurrentGameID; SET @CurrentGameID = NULL; -- select the random bot SELECT @userID = userID FROM users WHERE FIND_IN_SET('bot',baseInfo) ORDER BY RAND() LIMIT 0,1; -- update the game UPDATE online_games SET userID = @userID WHERE id = @CurrentGameID; -- select the next game to handle SELECT @CurrentGameID = id FROM online_games WHERE id > LastGameID ORDER BY id LIMIT 0,1; END WHILE; SET output = "done"; END;// CALL setFakeUsers(@status); SELECT @status; 

Essa é uma variação das respostas já fornecidas, mas deve ter melhor desempenho, pois não exige ORDER BY, COUNT ou MIN / MAX. A única desvantagem dessa abordagem é que você deve criar uma tabela temporária para manter todos os IDs (a suposição é que você tem lacunas na sua lista de IDs de clientes).

Dito isso, eu concordo com Mark Powell, embora, em geral, uma abordagem baseada em conjuntos ainda deva ser melhor.

 DECLARE @tmp table (Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, CustomerID INT NOT NULL) DECLARE @CustomerId INT DECLARE @Id INT = 0 INSERT INTO @tmp SELECT CustomerId FROM Sales.Customer WHILE (1=1) BEGIN SELECT @CustomerId = CustomerId, @Id = Id FROM @tmp WHERE Id = @Id + 1 IF @@rowcount = 0 BREAK; -- call your sproc EXEC dbo.YOURSPROC @CustomerId; END 

Caso o pedido seja importante

 --declare counter DECLARE @CurrentRowNum BIGINT = 0; --Iterate over all rows in [DataTable] WHILE (1 = 1) BEGIN --Get next row by number of row SELECT TOP 1 @CurrentRowNum = extendedData.RowNum --here also you can store another values --for following usage --@MyVariable = extendedData.Value FROM ( SELECT data.* ,ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RowNum FROM [DataTable] data ) extendedData WHERE extendedData.RowNum > @CurrentRowNum ORDER BY extendedData.RowNum --Exit loop if no more rows IF @@ROWCOUNT = 0 BREAK; --call your sproc --EXEC dbo.YOURSPROC @MyVariable END 

Uma solução melhor para isso é

  1. Copiar / código passado do procedimento armazenado
  2. Junte esse código com a tabela para a qual você deseja executá-lo novamente (para cada linha)

Isso foi você obter uma saída formatada em tabela limpa. Enquanto você executa o SP para cada linha, você obtém um resultado de consulta separado para cada iteração que é feio.

Eu tinha um código de produção que só conseguia lidar com 20 funcionários de uma vez, abaixo está a estrutura para o código. Acabei de copiar o código de produção e removi as coisas abaixo.

 ALTER procedure GetEmployees @ClientId varchar(50) as begin declare @EEList table (employeeId varchar(50)); declare @EE20 table (employeeId varchar(50)); insert into @EEList select employeeId from Employee where (ClientId = @ClientId); -- Do 20 at a time while (select count(*) from @EEList) > 0 BEGIN insert into @EE20 select top 20 employeeId from @EEList; -- Call sp here delete @EEList where employeeId in (select employeeId from @EE20) delete @EE20; END; RETURN end 

Eu gosto de fazer algo semelhante a isso (embora ainda seja muito semelhante a usar um cursor)

[código]

 -- Table variable to hold list of things that need looping DECLARE @holdStuff TABLE ( id INT IDENTITY(1,1) , isIterated BIT DEFAULT 0 , someInt INT , someBool BIT , otherStuff VARCHAR(200) ) -- Populate your @holdStuff with... stuff INSERT INTO @holdStuff ( someInt , someBool , otherStuff ) SELECT 1 , -- someInt - int 1 , -- someBool - bit 'I like turtles' -- otherStuff - varchar(200) UNION ALL SELECT 42 , -- someInt - int 0 , -- someBool - bit 'something profound' -- otherStuff - varchar(200) -- Loop tracking variables DECLARE @tableCount INT SET @tableCount = (SELECT COUNT(1) FROM [@holdStuff]) DECLARE @loopCount INT SET @loopCount = 1 -- While loop variables DECLARE @id INT DECLARE @someInt INT DECLARE @someBool BIT DECLARE @otherStuff VARCHAR(200) -- Loop through item in @holdStuff WHILE (@loopCount <= @tableCount) BEGIN -- Increment the loopCount variable SET @loopCount = @loopCount + 1 -- Grab the top unprocessed record SELECT TOP 1 @id = id , @someInt = someInt , @someBool = someBool , @otherStuff = otherStuff FROM @holdStuff WHERE isIterated = 0 -- Update the grabbed record to be iterated UPDATE @holdAccounts SET isIterated = 1 WHERE id = @id -- Execute your stored procedure EXEC someRandomSp @someInt, @someBool, @otherStuff END 

[/código]

Note que você não precisa da identidade ou da coluna isIterated em sua tabela temp / variable, eu simplesmente prefiro fazê-lo desta maneira para que eu não tenha que deletar o primeiro registro da coleção enquanto eu faço iterações através do loop.