By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,808 Members | 1,481 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,808 IT Pros & Developers. It's quick & easy.

Sql while break error

P: 18
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?
Attached Files
File Type: docx NAR.docx (13.6 KB, 129 views)
Nov 20 '16 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. WHILE ((SELECT AVG(ListPrice) FROM dbo.DimProduct) < $300)  
  2. BEGIN  
  3.     UPDATE DimProduct  
  4.         SET ListPrice = ListPrice * 2;  
  5.      IF ((SELECT MAX(ListPrice) FROM dbo.DimProduct) > $500)  
  6.          BREAK;  
  7. 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.

Share this Question
Share on Google+
1 Reply


jforbes
Expert 100+
P: 1,107
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:
Expand|Select|Wrap|Line Numbers
  1. WHILE ((SELECT AVG(ListPrice) FROM dbo.DimProduct) < $300)  
  2. BEGIN  
  3.     UPDATE DimProduct  
  4.         SET ListPrice = ListPrice * 2;  
  5.      IF ((SELECT MAX(ListPrice) FROM dbo.DimProduct) > $500)  
  6.          BREAK;  
  7. 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.
Nov 20 '16 #2

Post your reply

Sign in to post your reply or Sign up for a free account.