Often, T-SQL developers will ask if there is a way to access some kind of a call stack at runtime. Either the full call stack or simply the previous calling procedure. Some uses for this would be to debug a procedure, or to restrict users from updating a table unless it was done through a particular stored procedure.
As of SQL Server 2008, no feature exists. While searching the web for such a feature, I came across a feature request from 2006 by Erland Sommarskog. The feature has not yet been added, so we’ll just have to make do with workarounds. In this tip, I will show you how to make your own callstack using CONTEXT_INFO().
Mentioned as a workaround to the feature request above, is the use of SET CONTEXT_INFO and CONTEXT_INFO(). SET CONTEXT_INFO has been available since SQL Server 2000, but it may not be widely known. It sets 128 bytes of binary information tied to the current session. You can use CONTEXT_INFO() to retrieve this value anytime during the same session and see what you’ve set. NOTE: The CONTEXT_INFO() function is not available in SQL Server 2000, but you can retrieve the value from the CONTEXT_INTO column in the sysprocesses table (WHERE SPID = @@SPID).
I decided to see what I could create that would be easiest to implement, yet provide as much information as possible. Here is what I have come up with:
I decided that since I was trying to make a call stack, that using CONTEXT_INFO as a stack (LIFO) was the best option. Being a stack, I needed two procedures. CallStackPush, and CallStackPop. I also created a table valued function called CallStackView.
The idea is that you add a call to CallStackPush at the top of each of your stored procedures
EXEC dbo.CallStackPush @@PROCID
and a call to CallStackPop at the bottom.
EXEC dbo.CallStackPop
Then, anywhere you need it, you can call CallStackView to see the call stack.
SELECT * FROM dbo.CallStackView()
Which produces this:
In this case, SP_1 calls SP_2, which calls SP_3. Within SP_3, I added the statement to select from the CallStackView() function.
Given the small amount of space available, I was unable to add the parameter values into the call stack. The way I implemented it, I was able to show the schema, procedure name, and anything else available in the system views and tables based on the object_id of the stored procedures. This method allows for the nesting of up to 32 procedures (given the 4 byte (integer) object_id of stored procedures). I seriously doubt anyone will ever see nesting levels that big. If you do, you have some serious design changes to make.
The CallStackPush function contains only 3 lines of code.
DECLARE @BIN VARBINARY(128)
SELECT @BIN = CONVERT(BINARY(4), @PROCID) + ISNULL(CONTEXT_INFO(), CAST('' AS VARBINARY(1)))
SET CONTEXT_INFO @BIN
In the function, I convert the @PROCID parameter (INT) to a 4 byte binary and tack it onto the beginning of the stack. Then I put the whole thing back into CONTEXT_INFO.
The CallStackPop is a little more complicated, but still very small.
DECLARE @BIN VARBINARY(128)
SELECT @BIN = ISNULL(CONTEXT_INFO(), CAST('' AS VARBINARY(1)))
SELECT @BIN = SUBSTRING(@BIN, 5, 128)
SET CONTEXT_INFO @BIN
In this function, I just remove the first 4 bytes from CONTEXT_INFO.
CallStackView is simply a while loop to get a list of all 4 byte object ids that have been added to the value.
CREATE FUNCTION dbo.CallStackView()
RETURNS @result TABLE (
SchemaId INT,
SchemaName VARCHAR(256),
ProcedureId INT,
ProcedureName VARCHAR(256)
)
AS
BEGIN
DECLARE @BIN VARBINARY(128)
SELECT @BIN = ISNULL(CONTEXT_INFO(), CAST('' AS VARBINARY(1)))
DECLARE @PROCID INT
WHILE (LEN(@BIN) > 0 AND CONVERT(INT, SUBSTRING(@BIN, 1, 4)) > 0) BEGIN
SET @PROCID = CONVERT(INT, SUBSTRING(@BIN, 1, 4))
SET @BIN = SUBSTRING(@BIN, 5, 128)
INSERT @result (
SchemaId,
SchemaName,
ProcedureId,
ProcedureName
)
SELECT
s.schema_id,
s.name,
o.object_id,
o.name
FROM sys.objects o
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE o.object_id = @PROCID
END
RETURN
END
GO
This works well, and as I stated above, unless you have a nesting level greater than 32, you’ll be ok.
There is one thing this method does not do. It does not show the originating statement sent by the client. I needed to create another stored procedure for that. I called it GetOriginatingStatement. The code for it looks like this:
CREATE TABLE #inp_buff (
EventType NVARCHAR(30),
Parameters INT,
EventInfo NVARCHAR(255)
)
INSERT INTO #inp_buff
EXEC('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS')
SELECT
@RETVAL = EventInfo
FROM #inp_buff
DROP TABLE #inp_buff
This procedure will return (VIA the @RETVAL output parameter) the text of the outermost statement (the last statement sent by the client). You can use this in conjunction with the call stack functions above to get a complete picture of the stack.
Download Source Code: CallStack.zip