CODEWITHSUNDEEP
sqlstored-proceduresrecursion
imTony
imTony

Reputation: 42

Call stored procedure itself sql

ALTER PROCEDURE dbo.CurePeriod 
@curDate_p1 datetime

-- call
exec CurePeriod @curDateTrick

Can I call itself in stored procedure ? Its right? Many thank.

Upvotes: 2

Views: 6873

Answers (3)

Joachim Prinsloo
Joachim Prinsloo

Reputation: 628

You can do this but you will have to somehow manage how you will break out of the execution (if at all possible).

If you create a stored procedure, execute said procedure within itself and then execute it, it will just execute infinately untill you are met with a 

Maximum stored procedure, function, trigger, or view nesting level exceeded

error message.

In 99% of cases, there will be a better way. Rather post a question of what you are trying to achieve and showing what you have tried and have people suggest better ways of getting the desired result.

Upvotes: 2

dmoney
dmoney

Reputation: 881

Use extreme caution when calling a stored procedure within itself, infinite iterations can cause some major problems depending on what else is being done in the proc.

If you use conditions within the proc to ensure this is only executed the number of times you want then you can get around this.

BEGIN
IF @TEST = 'VALUE'
    EXEC PROCNAME @PROCPARAM
ELSE
    'SOMETHING ELSE'
END IF ;

Upvotes: 1

FDavidov
FDavidov

Reputation: 3675

Recursion is supported in many DBMSs and, in principle, its implementation is much like you wrote.

In your example, an exit condition is required to avoid infinite depth of recursion, so you should have something like the following:

CREATE PROCEDURE dbo.CurePeriod (@curDate_p1 datetime , @OtherParam INT)
AS

  BEGIN
      IF @OtherParam < 10 THEN
          exec CurePeriod (@curDateTrick , @OtherParam + 1) ;
      END IF ;
  END ;

and somewhere else in your code:

:
:
exec CurePeriod (@curDateTrick , 1) ;
:
:

Notes:

(1) Your example appears to be for SQL-Server and hence you may need to do some editing to the example I'm posting here.

(2) I'm forcing the recursion to end after 10 invocations.

Upvotes: 1

Related Questions