How to assign variable in WHILE EXISTS() Loop

Question

I would like to iterate through table rows where a certain condition is met. On each iteration I would like to assign two of the current row's value to a variable. This is what I got so far:

    WHILE EXISTS(SELECT TOP 1 *
FROM [Communications] c
WHERE [communicationTypeID] = 2
AND [status] = 0)

SET @communicationId = c.[id]
SET @message = c.[Message]
BEGIN
....

It display an error:

Msg 4104, Level 16, State 1, Line 25
The multi-part identifier "c.id" could not be bound.
Msg 4104, Level 16, State 1, Line 26
The multi-part identifier "c.Message" could not be bound.

Could someone please direct me to the correct direction? I am pretty new to SQL. Thank you in advance. Peter

Answer 1

It should seems like: (Be carefull and do not run infinite loop... Update comms properly in your stuff inside while loop , or delete id after your stuff)

WHILE EXISTS(SELECT TOP 1 *
    FROM [Communications] c
    WHERE [communicationTypeID] = 2
    AND [status] = 0)


BEGIN

SET @communicationId = (SELECT TOP 1 [id] FROM [Communications] WHERE [communicationTypeID] = 2 AND [status] = 0)
SET @message = (SELECT [Message] FROM [Communications] WHERE [id] = @communicationId )

/*Do your stuff here*/

DELETE FROM [Communications] WHERE [id] = @communicationId -- only if you need to delete...

END

Or you can use cursor:

DECLARE ExampleCursor CURSOR FOR
    SELECT [id], [Message]
        FROM [Communications] c
        WHERE [communicationTypeID] = 2
              AND [status] = 0

OPEN ExampleCursor
FETCH NEXT FROM ExampleCursor INTO @communicationId,@message

WHILE @@FETCH_STATUS = 0
BEGIN

/*do your stuff here by using @communicationId and @message for example*/ 
INSERT INTO NextTable (addParams)
SELECT addParams
FROM [Communications]
WHERE id = @communicationId

END


CLOSE ExampleCursor;
DEALLOCATE ExampleCursor;