How do I use a set-based approach to a complicated SQL insert

Question

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 24-Nov-21 21:52pm

Jackie Lloyd 2021

Updated 24-Nov-21 22:14pm

Richard Deeming

v2

Add a Solution

Comments

Jörgen Andersson 25-Nov-21 4:10am

What does HPM.AddMonthsToDateAndCreateStage do?

Jackie Lloyd 2021 26-Nov-21 2:07am

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 26-Nov-21 5:13am

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 26-Nov-21 8:15am

Thanks for taking the time to read my question.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2021-11-24T22:14:00

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
;