What is the maximum nesting level of a stored procedure in Sybase ASE?

Its a very obvious question for a Sybase developer to be faced with this kind of a question - What is the maximum level to which a stored procedures can be nested. Well the answer to somebody could be as short and specific as just run the following config system sp - sp_configure and search for "nested level" and you should see the right answer. This option is usually reflected as 16 however could be changed to some other value < 16.

But what if someone simply says - Well, I dont just believe figures, so can you just tell me a quick implementation of how exactly this works, to which simply say - Yes, Ofcourse!

So, in this article we will try creating two short and specific examples to demonstrate the maximum nesting levels of a stored procedure.

In the first example we will try creating a simple recursive stored procedure and see where it breaks. The simple stored procedure code is as follows -

create procedure proc1
as
begin
  exec proc1
end
go

As we can see in the above case, the above  dummy stored procedure simply does nothing but calls the itself recursively without any break condition which means that this should enter into an infinite loop of recursion till the whole memory gets drained up. But lets see what happens when we try to run this stored procedure -

exec proc1
go

Well, we are faced with the following error -

“Maximum stored procedure nesting level exceeded (limit 16).”

suggesting that we can recursively call a stored procedure only 15 number of times as 16th is the first call to the stored procedure. Simple isn't it to demonstrate this simple concept.

Now, we will move on to the indirect recursion or looping or more specific to say - circular reference scenario in stored procedure. Here, we will do the following - We will create a stored procedure proc1 which will call another stored procedure proc2 from within and then proc2 will call proc1 from the code hence creating a circular reference. What we will try to see here is that is the nesting level only implemented for one stored procedure or to a total number of stored procedures called in a go. So here is the example -

Create a procedure proc1 as –

create procedure proc1

as

begin

  exec proc2

end

go

This procedure is calling proc2 procedure which still does not exists in system catalogue. When we try to execute the above statements the Sybase ASE server gives the following error –

“Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'P2'. The stored procedure will still be created.”

The error suggests that apart from adding entries to the sysdepends table the procedure proc1 got created successfully which also means that sp_sysdepends wont show proc2 as the sp on which proc1 depends.

Now try creating the stored procedure proc2 with the following definition –

create procedure proc2

as

begin

  exec proc1

end

go

This will create procedure proc2 without any errors. Why? Because proc1 exists in the system catalogue but proc2 was not existing till this point in time and so - No Errors!

Now we will simply execute the procedure proc1 and see what happens.

exec proc1

Well, this will result in an error as we have created a circular reference without any terminating condition. The error returned is –

“Maximum stored procedure nesting level exceeded (limit 16).”

This also means that nesting level of 16 was applied to the combination of both the stored procedures. Well, thats all for today will be back with more of simple examples to illustrate more Sybase concepts soon.... Till then bbye and have fun.