Sql while break error

[madmax262]

Hi, I have developed a VB script which loops and updates the values in multiple fields by utilising dynamically naming the field name within a loop.

The procedure works correctly in MS Access and I am now trying to port it to SQL. I have managed to get the script to work down to the green highlighted text in the attached Word doc, but from there on I have an issue with a WHILE loop and a BREAK command.

Can anyone assist me with this?

[jforbes]

When you use branching statements, they typically only execute the Next Statement. If you want them to include multiple Statements, you need to use a BEGIN and END to delineate what Statements to include in the Branch statement.

The Example from Break:

WHILE ((SELECT AVG(ListPrice) FROM dbo.DimProduct) < $300)  
BEGIN  
    UPDATE DimProduct  
        SET ListPrice = ListPrice * 2;  
     IF ((SELECT MAX(ListPrice) FROM dbo.DimProduct) > $500)  
         BREAK;  
END 

The BEGIN and END group the two statements together, then the WHILE can act on both Statements.

Also, for future reference, please use the use the [CODE/] button and post the code inline with your Question, it makes it much, much easier to Address.