SQL Appeler une procédure stockée pour chaque ligne sans utiliser de curseur

Question

Comment peut-on appeler une procédure stockée pour chaque ligne d'un tableau, où les colonnes d'une ligne sont des paramètres d'entrée pour la procédure stockée ? sans en utilisant un curseur ?

Answer 1

D'une manière générale, je recherche toujours une approche basée sur les ensembles (parfois au prix d'une modification du schéma).

Cependant, cet extrait a sa place

-- 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

Answer 2

Vous pourriez faire quelque chose comme ceci : ordonner votre table par exemple par CustomerID (en utilisant la méthode AdventureWorks). Sales.Customer ), et itérer sur ces clients à l'aide d'une boucle 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

Cela devrait fonctionner avec n'importe quelle table tant que vous pouvez définir une sorte d'un ORDER BY sur une certaine colonne.

Answer 3

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)

D'accord, je ne mettrais jamais un tel code en production, mais il répond à vos exigences.

Answer 4

La réponse de Marc est bonne (je la commenterais si je savais comment faire !). J'ai juste pensé qu'il serait peut-être mieux de modifier la boucle pour que le "select" n'existe qu'une fois. (dans un cas réel où j'ai eu besoin de faire cela, la sélection était assez complexe, et l'écrire deux fois était un problème de maintenance risqué).

-- 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 <> @LastCustoemrID
  BEGIN
      -- call your sproc
  END

END

Answer 5

Si vous pouvez transformer la procédure stockée en une fonction qui renvoie un tableau, vous pouvez alors utiliser l'application croisée.

Par exemple, si vous disposez d'une table de clients et que vous souhaitez calculer la somme de leurs commandes, vous pouvez créer une fonction qui prend un identifiant de client et renvoie la somme.

Et tu pourrais faire ça :

SELECT CustomerID, CustomerSum.Total

FROM Customers
CROSS APPLY ufn_ComputeCustomerTotal(Customers.CustomerID) AS CustomerSum

Où la fonction ressemblerait :

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

Évidemment, l'exemple ci-dessus pourrait être réalisé sans fonction définie par l'utilisateur dans une seule requête.

L'inconvénient est que les fonctions sont très limitées - de nombreuses fonctionnalités d'une procédure stockée ne sont pas disponibles dans une fonction définie par l'utilisateur, et la conversion d'une procédure stockée en fonction ne fonctionne pas toujours.