Home > SQL Server > Get the nesting level value from procedure using @@NestLevel – SQL Server

Get the nesting level value from procedure using @@NestLevel – SQL Server


@@NestLevel : This function returns the current nesting level value of the procedure such as stored procedure and function. It returns an integer value. The initial value of this function is 0 and the maximum value it can go is 32.

This nest level value is auto incremental. If we call a procedure within another procedure then it increments by 1 or if we use sp_executesql to execute procedure then it increment by current value + 2.

This next level function increments its value only inside the procedure otherwise it will return value 0.

Implementation

User defined stored recursive procedure

Create PROCEDURE [dbo].[sp_myparentprocedure]
AS
BEGIN
SET NOCOUNT ON;

print ‘Nest Level ‘ + cast(@@nestlevel as varchar(3))
exec sp_myparentprocedure

END

Fetch the nest level value from the stored procedure by executing recursive procedure.

pic1

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: