How to Jump back to the loop increment stage inside a stored procedure ,If a codition is satisfied inside the loop ?

Hi ,
I have a stored procedure as shown below which has a loop running inside it. If the condition mentioned below is satisfied i need to go back to the loop and start from the Loop stage. How can I acheive this....

..... some steps.........
while (@iv<= @rcnt)
Begin
CREATE TABLE #MathLogicTable
(
IDNUM INTEGER IDENTITY(1,1),
ATTRIBUTENAME VARCHAR(256),
INPUTVALUES DECIMAL(15,3)
)
      
INSERT INTO #MathLogicTable
      SELECT  statements..................

if (not exists (select 1 from #MathLogicTable))
BEGIN
set @iv=@iv+1 (I need this step to go back to the start of the loop...if the condition satisfies)
END
------------------------------------------------------------
select............
update........
N Steps.........
-------------------------------------------------------------
End
Gowtham RamamoorthyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
Use Break, like
USE AdventureWorks2008R2;
GO
WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300
BEGIN
   UPDATE Production.Product
      SET ListPrice = ListPrice * 2
   SELECT MAX(ListPrice) FROM Production.Product
   IF (SELECT MAX(ListPrice) FROM Production.Product) > $500
      BREAK
   ELSE
      CONTINUE
END
PRINT 'Too much for the market to bear';

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian CroweDatabase AdministratorCommented:
Why don't you just put the rest of the logic after that point in an ELSE block?

if (not exists (select 1 from #MathLogicTable))
BEGIN
set @iv=@iv+1 (I need this step to go back to the start of the loop...if the condition satisfies)
END
ELSE
BEGIN
------------------------------------------------------------
select............
update........
N Steps.........
-------------------------------------------------------------
END
End
0
grendel777Commented:
You can use "BREAK" or you can put the "other stuff" into the IF statement as below. I also moved the temp table out of the loop so you're not re-creating it in every iteration:

CREATE TABLE #MathLogicTable 
(
	IDNUM INTEGER IDENTITY(1,1),
	ATTRIBUTENAME VARCHAR(256),
	INPUTVALUES DECIMAL(15,3)
)

WHILE (@iv <= @rcnt)
BEGIN
	TRUNCATE TABLE #MathLogicTable 
      
	INSERT INTO #MathLogicTable (ATTRIBUTENAME, INPUTVALUES)
	SELECT 'foo', 'bar'
	FROM SomeTables

	IF @@ROWCOUNT > 0
	BEGIN
		------------------------------------------------------------
		select............
		update........
		N Steps.........
		-------------------------------------------------------------
	END

	SET @iv += 1

END

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.