Larry Steinle

A call stack can be an invaluable tool for any developer’s toolbox. The call stack shows from point-of-entry to the currently executing procedure what methods were called giving helpful hints as to where a problem in the logic hides. While the call stack is readily available to an Oracle object via the DBMS_Utility.Format_Call_Stack method, as of Microsoft SQL Server 2012 there is no such capability. In today’s article I will demonstrate how to make your own DBMS_Utility.Format_Call_Stack method in SQL Server.

What is a Call Stack?

Let’s begin by understanding what call stack provides with an example. Say there are three stored procedures in SQL Server: Test1, Test2 and Test3. Test1 calls Test2 which calls Test3. We expect that when we check the call stack from Test1 we only see Test1. Check the call stack from Test2 and we will see Test1 and Test2. Again, check the call stack from Test3 and we will see Test1, Test2 and Test3 in the order by which they were called in the call stack. This behavior is currently unavailable in Microsoft SQL Server.

An example PL/SQL Call Stack from Oracle

----- PL/SQL Call Stack -----
object	line	object
id	number	name
407672500	15	[Sandbox].[Test1]
391672443	8	[Sandbox].[Test2]
375672386	5	[Sandbox].[Test3]

Strategy

I will be the first to admit that this is not going to be an elegant solution…What I am proposing today is a work-around. But when you need the ability to view the call stack, you need it. You do what you have to do to make your life easier for debugging and support.

What we will do is create a table to track the current stored procedure. Each stored procedure instance will be associated with a unique GUID. Whenever a call to a child procedure is made the procedure instances unique GUID will be passed to the child procedure. That’s the only ugly to this strategy. Every procedure you create must have a parameter where the caller’s guid can be passed into the callee’s method. Just before exiting the procedure we remove the record instance from the call stack table.

The Call Stack Table

Let’s begin by creating our call stack table where we will record the in-memory procedure instances.

CREATE SCHEMA Stack;
CREATE TABLE Stack.Trace(
	StackId    UNIQUEIDENTIFIER NOT NULL,
	ParentId   UNIQUEIDENTIFIER NULL,
	ProcId     INT NOT NULL,
	DatabaseId INT NOT NULL
);

We will track these customer administrative tools in a new schema called, Stack. The table will be named Trace and contain a column to record the current procedure instance and the caller parent procedure instance. The @@PROCID value representing the current object will be stored along with the database id value from the DB_ID method in the event that we want to organize this new behavior in a centralized, shared database.

Tracking The Current Running Procedure Instance

When entering a method we will need a routine to add the procedure instance into the table and another to remove the instance before exiting the routine. These routines will be called Push and Pop respectively.

In our push routine if a database id is not provided we will automatically assume the current database. The current instance GUID will be returned to the calling procedure via the output parameter @sID.

CREATE PROCEDURE Stack.Push
  @sID    UNIQUEIDENTIFIER = NULL OUTPUT,
  @ProcId INT,
  @pID    UNIQUEIDENTIFIER = NULL,
  @dbId   INT = NULL
AS BEGIN
  SET NOCOUNT ON;

  SET @sID = NEWID();
  SET @dbId = ISNULL(@dbId, DB_ID());

  IF (NOT EXISTS(SELECT 1 FROM Stack.Trace WHERE StackId = @pID))
    SET @pID = NULL;

  INSERT INTO Stack.Trace (StackId, ParentId, ProcId, DatabaseId)
  VALUES (@sID, @pID, @ProcId, @dbId);
END
GO

The Pop routine will simply remove the current instance from the stack trace table using the StackId returned from the Push routine.

CREATE PROCEDURE Stack.Pop
  @sID UNIQUEIDENTIFIER
AS BEGIN
  SET NOCOUNT ON;
  DELETE
    FROM Stack.Trace
   WHERE StackId = @sID;
END
GO

Building the Call Stack

Now that we have all the information required we can use a Common Table Expression (CTE) to construct the call stack. The CTE provides a very nice, simplified model to construct a recursive query. For additional information about recursive CTEs refer to Microsoft’s article, Recursive Queries Using Common Table Expressions.

  WITH CallStackCTE (RootId, StackId, ParentId, ProcId, ObjectName, Level) AS (
    --Anchor Definition
    SELECT StackId AS RootId,
           StackId,
           ParentId,
           ProcId,
           '[' + DB_NAME(DatabaseId) + '].[' 
               + OBJECT_SCHEMA_NAME(ProcId, DatabaseId) + '].[' 
               + OBJECT_NAME(ProcId, DatabaseId) + 
           ']',
           0 AS Level
      FROM Stack.Trace
     WHERE ParentId IS NULL
    UNION ALL
    --Recursive Definition
    SELECT CallStackCTE.RootId,
           Stack.Trace.StackId,
           Stack.Trace.ParentId,
           Stack.Trace.ProcId,
           '[' + DB_NAME(Stack.Trace.DatabaseId) + '].[' 
               + OBJECT_SCHEMA_NAME(Stack.Trace.ProcId, Stack.Trace.DatabaseId) + '].['
               + OBJECT_NAME(Stack.Trace.ProcId, Stack.Trace.DatabaseId) + 
           ']',
           Level + 1 AS Level
      FROM Stack.Trace
      JOIN CallStackCTE ON CallStackCTE.StackId = Stack.Trace.ParentId
  )

The very first column aliased as RootId tracks the entry routine instance. The RootId column is key to pulling together all the calls up to the current procedure instance without mixing in the call hierarchy from other call chains. The last column, Level, tells us where in the call chain we are located.

Two more methods will be required to get our formatted call stack: the first to identify which root the current call instance is part of and the second to get the call stack for the current call chain:

CREATE PROCEDURE Stack.GetRootId
  @rID UNIQUEIDENTIFIER OUTPUT,
  @sID UNIQUEIDENTIFIER
AS BEGIN
  SET NOCOUNT ON;

  --Use Common Table Expression to Iterate thru Recursive Table Relation
  --REF: http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
  WITH CallStackCTE (RootId, StackId, ParentId, Level) AS (
    --Anchor Definition
    SELECT StackId AS RootId,
           StackId,
           ParentId,
           0 AS Level
      FROM Stack.Trace
     WHERE ParentId IS NULL
    UNION ALL
    --Recursive Definition
    SELECT CallStackCTE.RootId,
           Stack.Trace.StackId,
           Stack.Trace.ParentId,
           Level + 1 AS Level
      FROM Stack.Trace
      JOIN CallStackCTE ON CallStackCTE.StackId = Stack.Trace.ParentId
  )

  --Get RootId From CTE
  SELECT @rID = CallStackCTE.RootId
    FROM CallStackCTE
   WHERE CallStackCTE.StackId = @sID;
END
GO
CREATE PROCEDURE Stack.GetCallStack
  @stack  VARCHAR(MAX) OUTPUT,
  @sID    UNIQUEIDENTIFIER,
  @indent BIT = 0
AS
  DECLARE @rID UNIQUEIDENTIFIER;
BEGIN
  SET NOCOUNT ON;
  SET @stack = '';
  SET @stack = @stack + '----- SQL Call Stack -----' + CHAR(13) + CHAR(10);
  IF @indent = 0 BEGIN
    SET @stack = @stack + 'proc' + CHAR(9) + 'line' + CHAR(9) + 'proc' + CHAR(13) + CHAR(10);
    SET @stack = @stack + 'id' + CHAR(9) + 'number' + CHAR(9) + 'name' + CHAR(13) + CHAR(10);
  END ELSE BEGIN
    SET @stack = @stack + 'proc' + CHAR(13) + CHAR(10);
    SET @stack = @stack + 'name' + CHAR(13) + CHAR(10);
  END;

  --Need the Root Id to identify all levels of the current stack trace
  EXECUTE Stack.GetRootId @rID = @rID OUTPUT, @sID = @sID;

  --Use Common Table Expression to Iterate thru Recursive Table Relation
  --REF: http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
  WITH CallStackCTE (RootId, StackId, ParentId, ProcId, ObjectName, Level) AS (
    --Anchor Definition
    SELECT StackId AS RootId,
           StackId,
           ParentId,
           ProcId,
           '[' + DB_NAME(DatabaseId) + '].[' 
               + OBJECT_SCHEMA_NAME(ProcId, DatabaseId) + '].[' 
               + OBJECT_NAME(ProcId, DatabaseId) + 
           ']',
           0 AS Level
      FROM Stack.Trace
     WHERE ParentId IS NULL
    UNION ALL
    --Recursive Definition
    SELECT CallStackCTE.RootId,
           Stack.Trace.StackId,
           Stack.Trace.ParentId,
           Stack.Trace.ProcId,
           '[' + DB_NAME(Stack.Trace.DatabaseId) + '].[' 
               + OBJECT_SCHEMA_NAME(Stack.Trace.ProcId, Stack.Trace.DatabaseId) + '].[' 
               + OBJECT_NAME(Stack.Trace.ProcId, Stack.Trace.DatabaseId) + 
           ']',
           Level + 1 AS Level
      FROM Stack.Trace
      JOIN CallStackCTE ON CallStackCTE.StackId = Stack.Trace.ParentId
  )

  --Get Stack
  SELECT @stack = @stack
                + CASE WHEN @Indent = 0
                    THEN 
                      CAST(CallStackCTE.ProcId AS VARCHAR(255)) + CHAR(9) + '--' + CHAR(9) + CallStackCTE.ObjectName + CHAR(13) + CHAR(10)
                    ELSE
                      CASE
                        WHEN Level = 0 THEN ''
                        ELSE REPLICATE(' ', (CallStackCTE.Level - 1) * 2) + '--> ' 
                      END
                     + CallStackCTE.ObjectName + CHAR(13) + CHAR(10)
                END
    FROM CallStackCTE
   WHERE CallStackCTE.RootId = @rID;
END
GO

In the GetCallStack method I provide an extra argument called @indent which can be used to control how we will format the call stack. When @indent is disabled with a value of 0 we will return the call stack using the same syntax as the Oracle, DBMS_Utility.Format_Call_Stack. When indent is enabled with a value of 1 we will return just the object names in an indented tree like layout.

Note that the only limitation in this example is that the code lacks support for line numbers. However, if you want, you can add another column to the table and an argument to the Push procedure to track the line number for the entry point.

Demonstration

Now all we need is a few routines to demonstrate how the call stack works! Three routines will be created, one calling the other. Each routine will print the call stack so that it is obvious how well our call stack works.

CREATE PROCEDURE Test1
  @pID UNIQUEIDENTIFIER = NULL
AS
  DECLARE @sID       UNIQUEIDENTIFIER;
  DECLARE @stackCall VARCHAR(MAX);
BEGIN
  SET NOCOUNT ON;
  EXECUTE Stack.Push @ProcId = @@PROCID, @pID = @pID, @sID = @sID OUTPUT;

  EXECUTE Stack.GetCallStack @sID = @sID, @stack = @stackCall OUTPUT;
  PRINT 'Stack Trace From Test1:' + CHAR(13) + CHAR(10);
  PRINT @stackCall;
  PRINT CHAR(13) + CHAR(10);

  EXECUTE Test2 @pID = @sID;
  EXECUTE Stack.Pop @sID = @sID;
END
GO
CREATE PROCEDURE Test2
  @pID UNIQUEIDENTIFIER = NULL
AS
  DECLARE @sID       UNIQUEIDENTIFIER;
  DECLARE @stackCall VARCHAR(MAX);
BEGIN
  SET NOCOUNT ON;
  EXECUTE Stack.Push @ProcId = @@PROCID, @pID = @pID, @sID = @sID OUTPUT;

  EXECUTE Stack.GetCallStack @sID = @sID, @stack = @stackCall OUTPUT;
  PRINT 'Stack Trace From Test2:' + CHAR(13) + CHAR(10);
  PRINT @stackCall;
  PRINT CHAR(13) + CHAR(10);

  EXECUTE Test3 @pID = @sID;
  EXECUTE Stack.Pop @sID = @sID;
END
GO
CREATE PROCEDURE Test3
  @pID UNIQUEIDENTIFIER = NULL
AS
  DECLARE @sID       UNIQUEIDENTIFIER;
  DECLARE @stackCall VARCHAR(MAX);
BEGIN
  SET NOCOUNT ON;
  EXECUTE Stack.Push @ProcId = @@PROCID, @pID = @pID, @sID = @sID OUTPUT;

  EXECUTE Stack.GetCallStack @sID = @sID, @stack = @stackCall OUTPUT;
  PRINT 'Stack Trace From Test3:' + CHAR(13) + CHAR(10);
  PRINT @stackCall;
  PRINT CHAR(13) + CHAR(10);

  EXECUTE Stack.Pop @sID = @sID;
END
GO

Now all that we have to do is run the test!

EXECUTE Test1;
GO

The following data is output from our call stack trace:

Stack Trace From Test1:
----- SQL Call Stack -----
proc	line	proc
id	number	name
407672500	--	[Sandbox].[dbo].[Test1]

Stack Trace From Test2:
----- SQL Call Stack -----
proc	line	proc
id	number	name
407672500	--	[Sandbox].[dbo].[Test1]
391672443	--	[Sandbox].[dbo].[Test2]

Stack Trace From Test3:
----- SQL Call Stack -----
proc	line	proc
id	number	name
407672500	--	[Sandbox].[dbo].[Test1]
391672443	--	[Sandbox].[dbo].[Test2]
375672386	--	[Sandbox].[dbo].[Test3]

Enable the @indent argument for the GetCallStack method and you will get the next call stack print:

Stack Trace From Test1:
----- SQL Call Stack -----
proc
name
[Sandbox].[dbo].[Test1]

Stack Trace From Test2:
----- SQL Call Stack -----
proc
name
[Sandbox].[dbo].[Test1]
--> [Sandbox].[dbo].[Test2]

Stack Trace From Test3:
----- SQL Call Stack -----
proc
name
[Sandbox].[dbo].[Test1]
--> [Sandbox].[dbo].[Test2]
    --> [Sandbox].[dbo].[Test3]

Summary

While not the most elegant means by which to get the call stack, we have created a very handy call stack model that emulates the Oracle, DBMS_Utility.Format_Call_Stack method. I did see a forum post titled, Provide Function to Return the Entire Call Stack, in which a Mr. Tomar Verona of the SQL Engine Development team stated that they are considering adding the call stack capability to SQL Server 2014. With any luck we won’t require this workaround much longer!

Happy Coding!