asked on

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

ASKER CERTIFIED SOLUTION

Peter Chan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Crowe

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

grendel777

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