SQL - Loop in a Procedure

Question

I have made the following two procedures. One getting data from a view and the other is meant to make a loop. For some reason the procedures are not working properly. After 3 hours I cancelled the procedure. The view itself gets data within a minute.

Does any of you have any idea what is going wrong?

CREATE PROCEDURE GetShipData
    @shipCode NVARCHAR(50)
AS
BEGIN 
   
    WITH voyages AS
    (
        SELECT [vslCode], [voyNum], ... -- andere kolommen
        FROM [IceBerg].[MRV].[voyage_copy]
        WHERE vslCode = @shipCode
    ),
    schedule AS (
        SELECT ...
        FROM [IceBerg].[mrv].[vsched_copy] AS vc
        LEFT JOIN voyages ON voyages.vslCode = vc.ves_code
        WHERE vc.ves_code = @shipCode -- of wellicht 'vesselcode' afhankelijk van uw schema
    ),
    -- Other CTEs or subqueries
   
    -- Final Select

    SELECT * FROM ...
    -- Every vslcode in the query has been replaced by @shipcode
END

And I made another procedure:

CREATE PROCEDURE PopulateNewEventsLogistic
AS
BEGIN
    DECLARE @vslCodes TABLE (vslCode NVARCHAR(50));
    INSERT INTO @vslCodes (vslCode) VALUES ('V018'), ('V019'), ('V020'); -- Voeg hier de gewenste vslCodes toe

    DECLARE @currentVslCode NVARCHAR(50);

    WHILE (SELECT COUNT(*) FROM @vslCodes) > 0
    BEGIN
        SELECT TOP 1 @currentVslCode = vslCode FROM @vslCodes;

        -- Execute GetShipData for current vslcode
        INSERT INTO MRV.new_events_logistic
        EXEC GetShipData @currentVslCode;

        DELETE FROM @vslCodes WHERE vslCode = @currentVslCode;
    END
END;

The view is a complex view with 20 steps. However it takes only a minute to get the results of the view for a vslcode. It takes however 3 hours for the procedures and it is still not finished then.

Perhaps you guys can enlighten me what is going wrong.

I tried writing the procedures but it didn't help

Answer 1

Can you execute this procedure in your DBMS? If so try adding debug steps with PRINT() to see were the program gets stuck. You could also use GETDATE() before and after calling a procedure and print the DATEDIFF() to see how long each step inside the loop takes.

I can't see anything inherently wrong with the code that would make it get stuck in a loop.