SQL While Loop using Dynamic SQL

Question

I am attempting to use a WHILE loop with dynamic SQL. See code below.

I have a table [Users] that set out various conditions required to update fields in [DB1] with a Status Code. The code runs, however [DB1] only has updated records based on last row in the USERS table which suggests it is not looping through the dynamic SQL variables.

Would anyone be able to advise how to iterate over the [Users] table?

Please note I have coded this using a SQL Cursor solution, however attempting to use WHILE loop. Thanks.

DECLARE @Field VARCHAR(50)
DECLARE @FieldType VARCHAR(10)
DECLARE @Operator CHAR(5)
DECLARE @Value VARCHAR(50)
DECLARE @Status VARCHAR(10)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

DECLARE @Count INT = 1  
DECLARE @RowCount INT 
SET @RowCount = (SELECT COUNT(1) FROM [DB1])  

DECLARE @SQL VARCHAR(MAX)


WHILE @Count<=@RowCount

BEGIN

SELECT @Field = [Field]
,@FieldType= [FieldType]
,@Operator=[Operator]
,@Value=[Value]
,@Status=[Status]
,@StartDate=CONVERT(VARCHAR(11),[StartDate],106) 
,@EndDate=CONVERT(VARCHAR(11),[EndDate],106)

FROM [USERS] (NOLOCK)
ORDER BY [ConfigOrder] ASC


        SET @SQL= 'UPDATE [DB1]
        SET [Status] = ''' + @Status + '''' +
        'FROM [DB1] AS a (NOLOCK)
        INNER JOIN [DB2] AS b (NOLOCK) ON a.ID = b.ID
        WHERE b.[status] = ''UNK''
        AND ' + @Field + '' + @Operator + '' + @Value +
        'AND  a.[start_date] >= ''' + CONVERT(VARCHAR(11),@StartDate,106)  + '''' +
        'AND  a.[start_date] <= ''' + CONVERT(VARCHAR(11),@EndDate,106)  + '''' ;


EXEC (@SQL)

SET @Count=@Count+1

END

How does SELECT @Field = [Field] ,@FieldType= [FieldType] ,@Operator=[Operator] ,@Value=[Value] ,@Status=[Status] ,@StartDate=CONVERT(VARCHAR(11),[StartDate],106) ,@EndDate=CONVERT(VARCHAR(11),[EndDate],106) FROM [USERS] (NOLOCK) ORDER BY [ConfigOrder] ASC advance inside the loop? — siggemannen
– siggemannen, Commented Jul 16, 2024 at 11:53
Why arr you spamming th NOLOCK hint? What do you think it does against the table you are UPDATEing? How do you UPDATE a table while ignoring other locks? — Thom A
– Thom A ♦, Commented Jul 16, 2024 at 11:55
The logic was trying to get it to iterate over the variables in the Users table? However I am a novice with loops so may be completely on the wrong track. — SarahChapman
– SarahChapman, Commented Jul 16, 2024 at 11:56
I would also ask why do you want to use a loop at all; looping is normally the very last thing you should be doing. — Thom A
– Thom A ♦, Commented Jul 16, 2024 at 11:58
I suggest you take a step back and provide us with some sample data and expected results and explain the problem; you have likely gone in the very wrong direction here. — Thom A
– Thom A ♦, Commented Jul 16, 2024 at 12:13

Charlieface · Accepted Answer · 2024-07-16 13:26:25Z

0

You have an implicit cross-join on DB1 because you are doing UPDATE DB1 not UPDATE a.

You shouldn't use WHILE just to avoid a cursor, it isn't any faster, if anything it's slower. Cursors are very slow, but manually writing one is even worse normally.

Also

You should properly parameterize your dynamic SQL, or you leave yourself open to syntax errors and injection issues.
Don't use NOLOCK, it has serious data integrity implications. Use snapshot isolation to avoid blocking, or use WITH (TABLOCK) for outright performance.
Dynamic SQL goes in a nvarchar(max) field. Column and object names should be sysname.
I don't know your intention, but it's normally best to use a half-open interval when comparing datetimes, like >= AND <.

DECLARE @crsr CURSOR;
DECLARE @SQL NVARCHAR(MAX);
DECLARE @Value VARCHAR(50);
DECLARE @Status VARCHAR(10);
DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;

SET @crsr = CURSOR FAST_FORWARD FOR
SELECT
  '
UPDATE d1
SET Status = @Status
FROM DB1 AS d1
INNER JOIN DB2 AS d2 ON d1.ID = d2.ID
WHERE d2.status = ''UNK''
  AND ' + u.Field + ' ' + u.Operator + ' @Value
  AND d1.start_date >= @StartDate
  AND d1.start_date <= @EndDate;
',
  u.Status,
  u.Value,
  u.StartDate,
  u.EndDate
FROM [USERS] u
ORDER BY
  ConfigOrder ASC;

OPEN @crsr;
WHILE 1=1
BEGIN
    FETCH @crsr INTO @SQL, @Status, @Value, @StartDate, @EndDate;
    IF @@FETCH_STATUS <> 0 BREAK;

    PRINT @SQL;    -- your friend

    EXEC sp_executesql @SQL,
      N'@Value VARCHAR(50),
        @Status VARCHAR(10),
        @StartDate DATETIME,
        @EndDate DATETIME',
      @Value = @Value,
      @Status = @Status,
      @StartDate = @StartDate,
      @EndDate = @EndDate;
END;

-- no need to deallocate cursor in variable

If it's possible to consolidate the Field and Operator conditions into just a few options then it might be faster to use STRING_AGG and just generate a single big SQL batch.

edited Jul 16, 2024 at 13:26

answered Jul 16, 2024 at 12:08

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SarahChapman Over a year ago

Thank you for the advice, really appreciated and will take these on board as cursors/loops are quite new for me.

Squirrel Over a year ago

@Charlieface you missed out the sp_executesql ?

Charlieface Over a year ago

whoops thanks, you should just edit that in next time :-)

Collectives™ on Stack Overflow

SQL While Loop using Dynamic SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related