O'Reilly logo

Microsoft® SQL Server™ 2000 Programming by Example by Carlos Eduardo Rojas, Fernando G. Guerrero

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required