stop execution of a recursive loop on some validation

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.

This is what [return statement](https://msdn.microsoft.com/en-us/library/ms174998.aspx) is for. — Andrew Savinykh, Feb 17 '15 at 06:49
[Global variables](http://stackoverflow.com/a/22372569/284111) — Andrew Savinykh, Feb 17 '15 at 06:51
@zespri yes but return statement not making the loop come out of the recursion. — Sarthak, Feb 17 '15 at 06:54
What do you mean? You have a [tail recursion](http://en.wikipedia.org/wiki/Tail_call) it will end naturally. — Andrew Savinykh, Feb 17 '15 at 06: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, Feb 17 '15 at 07:06
So when the sp ends all levels of recursion end. Problem solved. — Andrew Savinykh, Feb 17 '15 at 07:08
nopes actually in one of the condition i want the stored procedure to end immediately not going any further into the recursive calls and in the previous calls ad just show me an empty data table in which am inserting values. — Sarthak, Feb 17 '15 at 07:17

score 1 · Answer 1 · answered Feb 17 '15 at 07:50

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 '15 at 07:50

Remus Rusanu

288,378
40
442
569

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. – Sarthak Feb 17 '15 at 08:34

score 0 · Answer 2 · answered Feb 17 '15 at 07:25

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 '15 at 07:25

hussein shaib

108
1
9

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. – Sarthak Feb 17 '15 at 07:30

stop execution of a recursive loop on some validation

2 Answers2