SQL query with a loop and two IF

Question

I have a sql request below.
in the table [ServicesConf] there are 2 rows: ID = 1, 2, but after executing sql query to table [ServicesLog] 3 rows are inserted.

how to make only 2 rows be inserted?

DECLARE @cnt int = 1
       ,@max int = (SELECT COUNT(*) FROM [ServicesConf]);

WHILE @cnt <= @max
BEGIN
DECLARE @DateTimeNow datetime = GETDATE()
DECLARE @DateTimeNowTimeHM varchar(5) = FORMAT(@DateTimeNow, 'HH:mm') --CONVERT(varchar(5), GETDATE(),108)
DECLARE @DateTimeNowTimeM varchar(2) = FORMAT(@DateTimeNow, 'mm')
DECLARE @DateTimeNow_WeekDay varchar = (SELECT DATEPART(WEEKDAY,@DateTimeNow))
DECLARE @DateTimeNow_WeekDay_Check int = (SELECT 1 FROM [ServicesConf] WHERE [SCHEDULE_DAY] LIKE '%' + @DateTimeNow_WeekDay + '%' AND [ID] = @cnt)
DECLARE @DateTimeNow_WeekHour_Check int = (SELECT 1 FROM [ServicesConf] WHERE [SCHEDULE_HOUR] = @DateTimeNowTimeHM AND [ID] = @cnt)
DECLARE @DateTimeNow_Repeat_Check int = (SELECT 1 FROM [ServicesConf] WHERE [REPEAT] LIKE '%' + @DateTimeNowTimeM + '%' AND [ID] = @cnt)

    IF (@DateTimeNow_WeekDay_Check = 1
        AND @DateTimeNow_Repeat_Check = 1)
        BEGIN
        INSERT INTO [ServicesLog] ()
        SELECT *
        FROM [ServicesConf]
        WHERE [REPEAT] IS NOT NULL
        END

    IF (@DateTimeNow_WeekDay_Check = 1
        AND @DateTimeNow_WeekHour_Check = 1)
        BEGIN
        INSERT INTO [ServicesLog] ()
        SELECT *
        FROM [ServicesConf]
        WHERE [REPEAT] IS NULL
        END
    SET @cnt = @cnt + 1
END

Answer 1

Hi @Илья Листратенко ,

Welcome to Microsoft Q&A!

Please have a try with below and check whether it is helpful to you.

    IF (@DateTimeNow_WeekDay_Check = 1  
         AND @DateTimeNow_Repeat_Check = 1)  
         BEGIN  
         INSERT INTO [ServicesLog]   
         SELECT *  
         FROM [ServicesConf]  
         WHERE [REPEAT] IS NOT NULL  
		 and [ID] = @cnt   --adding this row  
         END  
      
     IF (@DateTimeNow_WeekDay_Check = 1  
         AND @DateTimeNow_WeekHour_Check = 1)  
         BEGIN  
         INSERT INTO [ServicesLog]   
         SELECT *  
         FROM [ServicesConf]  
         WHERE [REPEAT] IS NULL  
		 and [ID] = @cnt  --adding this row  
         END  

If above is still working, please provide the CREATE TABLE statements for your tables together with INSERT statements with sample data, and the expected result of the sample.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Try using WHERE [REPEAT] = @DateTimeNowTimeM instead of LIKE.