Passing the Values

Congratulations to Andrei Popovici, software engineer at Softure in Bucharest, Romania, and Mingshou Tang, DBA at Toronto-based DS-MAX International. Andrei won first prize of $100 for the best solution to the June Reader Challenge, "Passing the Values". Mingshou won second prize of $50. Here’s a recap of the problem and the solution to the June Reader Challenge.

Problem

As the database architect for a company that uses SQL Server 2000, Michael has been assigned the task of designing routines for auditing changes that the company’s Web application makes to the data. He decides to use triggers to implement the auditing routines and to use stored procedures for performing all data-manipulation operations in the application. Let’s simulate Robert’s scenario by using the following four CREATE statements to create two sample tables with relevant columns, a trigger, and a stored procedure:

CREATE TABLE Test ( i int primary key )CREATE TABLE TestAudit ( i int, Source varchar(10) )GOCREATE TRIGGER AuditTestInsertON TestAFTER INSERTASBEGIN   DECLARE @CallingProc varchar(128)— Robert needs to initialize this variable.   DECLARE @Source varchar(30) — Robert needs to initialize this variable.INSERT INTO TestAudit ( i )   SELECT i from insertedENDGOCREATE PROCEDURE InsertTest ( @Source varchar(30),         @i int )ASINSERT INTO test values( 1 )GO

For a specific business purpose, Michael wants to learn two kinds of information: from within the trigger code, he wants to find out the name of the stored procedure that fires the INSERT trigger, and from the audit trigger, he wants to discover the value of the @Source parameter that the application passes to the stored procedure each time the trigger fires. Michael expects that after the Web application calls the stored procedure he wrote with the following parameters:

EXEC InsertTest 'App1', 1

the audit table will contain the newly inserted row with its App1 source value. However, the row appears in the audit table without the information he needs, so Michael has to modify the code—preferably with minimal changes—so that it populates all the necessary columns. Help Michael design an efficient solution for passing values from the stored procedure to the trigger that requires minimal changes to the code.

Solution

Michael can use SQL Server 2000’s new SET CONTEXT_INFO statement to store session-specific values. This statement lets you store up to a 128-byte value in the context_info column of the sysprocesses system table in the master database. First, Michael can determine the stored procedure’s object identifier inside the stored procedure by using the @@PROCID global function. Next, he can store this value and the @Source parameter value for the session by using the SET CONTEXT_INFO statement. Because the SET CONTEXT_INFO statement can store only binary values, Michael needs to cast the @@PROCID and @Source parameter values to the binary data type. The following modified stored procedure performs those operations:

CREATE PROCEDURE InsertTest ( @Source varchar(10), @i int )ASDECLARE @Parameters varbinary(128)SET @Parameters = CAST( @@PROCID as binary(4) ) + CAST( @Source as varbinary(30))SET CONTEXT_INFO @ParametersINSERT INTO test VALUES( 1 )SET CONTEXT_INFO 0x0GO

Then, Michael can read the values that the stored procedure set from inside the trigger by querying the context_info column of the sysprocesses table. From the sysprocesses table, the query will retrieve only the row matching the current process ID that the @@SPID global function identifies. The query can then use the SUBSTRING() function to parse the values into local variables, as the following statement shows:

CREATE TRIGGER AuditTestInsert  ON TestAFTER INSERTASBEGIN   DECLARE @CallingProc varchar(128)   DECLARE @Source varchar(10)   SELECT @CallingProc = OBJECT_NAME( SUBSTRING        ( p.context_info, 1, 4 ) ),         @Source = CAST( SUBSTRING        ( p.context_info, 5, 128 ) AS varchar(10) )     FROM master..sysprocesses as p   WHERE p.spid = @@SPID   PRINT @CallingProc   INSERT INTO TestAudit ( i, Source )   SELECT i, @Source FROM insertedENDGO

After the query parses the values, Michael can obtain the name of the stored procedure by using the OBJECT_NAME() system function in the SELECT statement above. Finally, he can obtain the @Source parameter value by casting the binary value into local variables appropriately. The following sample code shows the trigger and stored-procedure code in action:

EXEC InsertTest 'App1', 1SELECT * FROM TestAudit

Other possible solutions to this problem include using a global cursor to store the values and to retrieve them from the trigger (instead of using the trigger on a view) and using a temporary table created in the stored procedure.

Now, test your SQL Server savvy in the July Reader Challenge, "Quickening the Query" (below). Submit your solution in an email message to [email protected] by June 19. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Here’s the challenge: Steve is troubleshooting a performance problem in a VBScript application that uses SQL Server 2000 and 7.0 as its database servers. During his review of a SQL query that the application generates, he notices in the query’s WHERE clause an IN logical operator containing a list of column values. Steve determines that the application’s slower processing stems from the large number of IN list values that the query checks and the significant overhead required for parsing the long list of values. The following query contains the IN operator in the WHERE clause:

SELECT t.x   FROM tbl as t   WHERE t.y IN ( 1, 2, 3, 4, 5, 6, 7 /* long list of IDs */ )

How can Steve improve this query’s performance? Devise a solution that works in SQL Server 2000 and 7.0.