Compare two variables in an SQL WHILE loop

Question

I am getting the error

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near '@PacketCode'.

when I try to execute. Something is wrong with my WHILE loop but I can't see what. Any suggestions?

DECLARE @PacketCode as varchar(255)
SET @PacketCode = 'ZZZ_Archive_TEST'

DECLARE @Value as varchar(255)
SET @Value = ''

DECLARE @i int
SET @i = 1

SELECT 
    @Value = (SELECT packetcode
              FROM tblScriptReports
              WHERE packetcode = @PacketCode)

WHILE (@PacketCode = @Value) 
BEGIN
    @PacketCode = @PacketCode + ' (' + @i + ')'

    SELECT @Value = (SELECT packetcode
                    FROM tblScriptReports
                    WHERE packetcode = @PacketCode)

    SET @i = @i + 1                   
END

SELECT @PacketCode

What have you tried? Have you checked documentation? Commented out lines until the error goes away and you know what the problem is? — sirdank
– sirdank, Commented Jun 6, 2017 at 17:40

Tab Alleman · Accepted Answer · 2017-06-06 17:15:32Z

4

In TSQL you assign variable values with the SET command.

So instead of this:

@PacketCode = @PacketCode + ' (' + @i + ')'

You must do this:

SET @PacketCode = @PacketCode + ' (' + @i + ')'

Funny that you did this correctly everywhere else in the code.

answered Jun 6, 2017 at 17:15

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

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

1 Comment

PC2014 Over a year ago

Yes this fixed it! Can't believe I missed it.

VDK · Accepted Answer · 2017-06-06 17:15:30Z

1

Try to add a SET in front of the @PacketCode in the BEGIN:

DECLARE @PacketCode as varchar(255)
set @PacketCode = 'ZZZ_Archive_TEST'

DECLARE @Value as varchar(255)
set @Value = ''

DECLARE @i int
set @i = 1

SELECT @Value = (SELECT packetcode
             FROM tblScriptReports
             WHERE packetcode = @PacketCode)

WHILE (@PacketCode = @Value) 

BEGIN

SET @PacketCode = @PacketCode + ' (' + @i + ')'

    SELECT @Value = (SELECT packetcode
                    FROM tblScriptReports
                    WHERE packetcode = @PacketCode)

    set @i = @i + 1                   
END

select @PacketCode

answered Jun 6, 2017 at 17:15

VDK

5833 silver badges8 bronze badges

Collectives™ on Stack Overflow

Compare two variables in an SQL WHILE loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related