Click here to Skip to main content
15,115,503 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am just starting off writing stored procedures and would like to start using the best practice of set-based programming rather than resorting to procedural.

My code uses a 'WHILE' loop which I am desperately trying to get rid of. It is making my head hurt and I don't know if it is me not getting it, or if it not possible. And I can't find anything on the internet which deals with this situation, all the examples are overly simple. Help please :)

I have simplified my example as much as possible by cutting the parameters down nd removing error handling etc.

What I have tried:

SQL
ALTER PROCEDURE [HPM].[Test] 
AS
BEGIN

  	DECLARE
	@Date DATE,
	@NextStage nvarchar(50),
	@stage_count int,
	@Date_NextStage datetime,
	@val nvarchar(10)

	SET @Date = GETDATE();

	-- insert the next 6 stages
	SET @stage_count = 0;	
	WHILE @stage_count < 6
	BEGIN;

		SET @NextStage = HPM.AddMonthsToDateAndCreateStage(@Date, @stage_count + 4);
		SET @Date_NextStage = DATEADD(m, @stage_count + 4, @Date);

		IF NOT EXISTS(SELECT 1 FROM HAS.HPM.StagesBackup WHERE HAS.HPM.StagesBackup.Stage = @NextStage)
		BEGIN
			SET @val = HPM.AddMonthsToDateAndCreateStage(@Date_NextStage, -120);

				INSERT INTO HAS.HPM.StagesBackup (STAGE, W1_HAS)
					VALUES (@NextStage, @val);				
		END;
		
		SET @stage_count = @stage_count + 1
	END;
END
Posted
Updated 4 days ago
v2
Comments
Jörgen Andersson 4 days ago
   
What does HPM.AddMonthsToDateAndCreateStage do?
Jackie Lloyd 2021 4 days ago
   
It's my own function which adds a number of months to the current date and uses the month and year values to create a 'stage number'. Sorry, I should have simplified my code to not have something unknown like this in it.
Jörgen Andersson 3 days ago
   
No worries, it was just unclear to me whether the six stages affected each other or not and therefore needed to be added sequentially.
Or if they only needed to look in the backup for earlier inserts
Jackie Lloyd 2021 3 days ago
   
Thanks for taking the time to read my question.

1 solution

Something like this should work:
SQL
DECLARE @Date date = GETDATE();

WITH cteStageCounts (stage_count) As
(
    SELECT 0
    UNION SELECT 1
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 4
    UNION SELECT 5
),
cteStages (NextStage, Date_NextStage) As
(
    SELECT
        HPM.AddMonthsToDateAndCreateStage(@Date, stage_count + 4),
        DateAdd(m, stage_count + 4, @Date)
    FROM
        cteStageCounts
),
cteStagesToCreate (NextStage, val) As
(
    SELECT
        NextStage,
        HPM.AddMonthsToDateAndCreateStage(Date_NextStage, -120)
    FROM
        cteStages As S
    WHERE
        Not Exists
        (
            SELECT 1
            FROM HAS.HPM.StagesBackup As B
            WHERE B.Stage = S.NextStage
        )
)
INSERT INTO HAS.HPM.StagesBackup 
(
    STAGE, 
    W1_HAS
)
SELECT
    NextStage,
    val
FROM
    cteStagesToCreate
;
   
Comments
phil.o 4 days ago
   
I love CTE's :)
CHill60 4 days ago
   
I also love CTEs and I hate loops in SQL! My 5
Jackie Lloyd 2021 4 days ago
   
Thank you, I really appreciate your help. I am just trying to digest this, it may take a while!
Jackie Lloyd 2021 4 days ago
   
Well, I've just about followed it through. Thank you, its a superb answer and has given me much to work on.
After many years of writing code (C++ etc.) I find that SQL blows my mind away - does it get easier?!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900