stop execution of a recursive loop on some validation [closed]

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

Want to improve this question? As written, this question is lacking some of the information it needs to be answered. If the author adds details in comments, consider editing them into the question. Once there's sufficient detail to answer, vote to reopen the question.

Closed 10 years ago.

Improve this question

I am having a recursive loop of a stored procedure and i want to stop the execution of that recursive loop on a validation and return an empty table. How can i do that?

This is some sample code :

alter procedure Searching
(parameters)
begin
  do something
  if(validation)
     --exit here
  else
    exec Searching(parameters);
end

and i have another question of how to declare a global variable in sql? which should not lose it's scope from one stored procedure to another.

@zespri yes but return statement not making the loop come out of the recursion. — Sarthak
– Sarthak, Commented Feb 17, 2015 at 6:54
What do you mean? You have a tail recursion it will end naturally. — Andrew Savinykh
– Andrew Savinykh, Commented Feb 17, 2015 at 6:56
yes kinda like that. the do something part is doing the logic part then recursion calling going for the same logic on the next column. — Sarthak
– Sarthak, Commented Feb 17, 2015 at 7:06

Remus Rusanu · Accepted Answer · 2015-02-17 07:50:32Z

1

T-SQL is a really poor choice for recursion. It won't allow more than 32 levels of nesting, see @@NESTLEVEL:

When the maximum of 32 is exceeded, the transaction is terminated.

As always, the answer is to think in sets, use a SELECT instead of a stored procedure. Read Recursive Queries Using Common Table Expressions.

As to why your example doesn't work: you obviously made a mistake in the code which you did not post.

answered Feb 17, 2015 at 7:50

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

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

1 Comment

Sarthak Over a year ago

well recursion is working fine in my case, the problem is in one of the condition i want it nt to do the logic steps further on and just terminate execution.

hussein shaib · Accepted Answer · 2015-02-17 07:25:34Z

0

you must break down the condition of the recursion so that it don't continue after you return. you can make use off an integer that is decremented after each loop when it is negative u can stop execution.

answered Feb 17, 2015 at 7:25

hussein shaib

1081 silver badge9 bronze badges

1 Comment

Sarthak Over a year ago

actually am already using a counter for the execution of the procedure on column, but the problem is something else, like actually am getting two values for each search on each column, when either of the two values comes as null, i want the procedure to stop the execution and just return an empty table.

Collectives™ on Stack Overflow

stop execution of a recursive loop on some validation [closed]

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related