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