Nesting Stored Procedures
Stored procedures can be nested up to 32 levels. Nested means that one stored procedure calls another, and so on. If the nesting level exceeds 32, the execution of the whole group of stored procedures fails. SQL Server provides a way to check the nesting level using the @@nestlevel system function.
When a stored procedure calls another stored procedure, the nesting level is incremented by one, and then when the inner stored procedure finishes its execution, the nesting level decreases by one.
Listing 8.27 shows the creation of two stored procedures. The first one, CheckSupplier, returns –1 if a given supplier name already exists in the Suppliers table, and 0 if it doesn't. The second one, InsertSupplier, calls the
Get Microsoft® SQL Server™ 2000 Programming by Example now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
