Executing process for all rows of a table in SQL SP

[ThomasMatelart]

Hi !

I'm trying to execute a stored procedure 'B' for each rows of a table in an other SQL stored procedure 'A'.
(The param on the stored procedure 'B' are the data stored in each rows of the table!)
How can I do a 'While' simulation ?

HELP ...

ThomasM

 

[tlbroadbent]

You can use WHILE in a SQL stored procedure. See BOL for details.

I'm not sure I understand your process. SQL works best on sets of data rather than processing one record at a time. You'll probably need to use a cursor to handle one record at a time.

I suggest that you post MS SQL Server questions in forum183. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[carp]

That will depend a lot on which RDBMS you are using.
If you are using Oracle, you can create a cursor in the first procedure. For each row in the cursor, invoke the second procedure.

 

[shnaeem]

I am facing similar situation. Have you come up with a solution. I am trying to get exactly same results.

If yes then please send reply to this post.

 

[Guest_imported]

CREATE PROCEDURE dbo.ReInitCommandFromDeadDaemon
(
@DaemonNr int
)
As

DECLARE @CECommand bigint
DECLARE @CERequest bigint
DECLARE @DateTime datetime

DECLARE Cur CURSOR FOR SELECT CERequest, CECommand FROM WIPSTATUS WHERE DemonNr = @DaemonNr AND Status = 1 OR Status = 3

OPEN Cur
IF (@@FETCH_STATUS <> -1)
begin
FETCH NEXT FROM Cur INTO @CERequest, @CECommand
end
WHILE (@@FETCH_STATUS <> -1)
begin
IF (@@FETCH_STATUS <> -2)
begin
SET @DateTime = GetDate()
/*Exécution de la procédure CommandInterrupt */
EXEC CommandInterrupt @CERequest, @CECommand, 0, @DaemonNr, @DateTime, 'Le daemon qui exécutait cette commande est mort !'

/*Mise à jour du status de la requête !*/
EXEC SetCurrentStatusRequest @CERequest, @CECommand
end
FETCH NEXT FROM Cur INTO @CERequest, @CECommand
end

CLOSE Cur
DEALLOCATE Cur
GO