ARTICLES

Home  > Articles  >  Enhance Your Transaction Flexibility With Embedded Stored Procedures

Enhance Your Transaction Flexibility With Embedded Stored Procedures

By Ben Taylor

Introduction

Recently I found a need to perform the same processes from different entry points in an application. Depending on the entry point the processes would be called one or more times. Because of the nature of the application it was necessary to assure all processes succeeded by embedding them in a transaction. It didn't take much time to recognize the potential duplication in code. I decided to break these portions of code into separate stored procedures and to call them from different stored procedures that would implement the desired transactions. This article demonstrates how to enforce transactions in a way that is readable and predictable by calling a stored procedure from within a parent stored procedure.

What Are Transactions?

Think of a transaction as the basic unit of work. Typically a unit of work consists of an Insert, Update or Delete in a table. Sometimes the desired unit of work is a series of Inserts, Updates or Deletes. Take a purchase order for example. When a purchase order is submitted to the database usually there is a master order record which has the totals for all the order, all parties participating in the order, shipping information, etc. In another table there are one or more line items representing each of the order details selected by the buyer.

When you commit a purchase order change to a database it is imperative that all modifications are completed so that the purchase order does not become corrupted. If you deleted a line item in a purchase order but did not update the order table then the summary values would not necessarily represent the totals from all the detail records. In this case the Unit of Work would be the deletion of the detail record and the update of the order record. You require that both the delete and update succeed or they both fail and the database is unchanged.

SQL Server Supports Transactions...So What's The Problem?

With the advent of COM+ most applications will implement transactions outside of SQL Server for a scenario like the purchase order example above. In this case the User Interface captures all of the purchase order information in some form of cache. Then when the order is submitted each of the items is sent through a data layer which establishes a transaction. The data layer makes all of the necessary calls to the database, one or more depending on how the database is configured, and then commits the transaction after the last modification has completed successfully. This method is especially useful when you are making changes on more than one database in which all modifications must succeed or fail. MSDN provides an example of transactions using multiple data stores with their Visual Studio .Net example Fitch & Mather 7.0.

These heterogeneous transactions are quite powerful, but require a lot of overhead when communicating between all of the servers involved. Microsoft uses a Distributed Transaction Coordinator to communicate when transactions are started and when they should be committed or rolled back. In the purchase order example the application begins a transaction and then has to make one call to the database to save the order header record and a separate call to the database to save each line item in the order after which the transaction is committed. If the user creates a purchase order with four line items the application has to make 5 calls to the database; One call to save the order header and four calls to save the line items. For the duration of this process including the time for the round trip the SQL Server cannot commit any changes to the database. This means that some resources are retained until the transaction has completed or failed. 

In some instances the overhead of Distributed Transaction Coordination is worth a little more effort in your stored procedure development. The purchase order scenario provided above would not be a good candidate because the units of work are pretty small. There are cases when you expect a heavy transaction load or have lots of records to be included in your transaction in which you find benefit from a different approach. This article demonstrates how you can maintain separate stored procedures and still utilize a transaction with only one database call from your middle tier. This works best when you have only one database. Even though you can use linked servers it is not a recommended practice because you have to hard code server names in the stored procedures. In this case other methods are preferred and are outside of the scope of this article. 

As a side note it is helpful to know how Microsoft implements transactions in SQL Server in respect to nesting. If a transaction is begun and a nested transaction is committed the nested commit is essentially ignored due to the other transaction. This makes sense when you consider the purpose of a transaction. The Duwamish 7.0 example at MSDN explains this in more detail and provides further reading.

A Real World Problem

One application I worked on allowed users to be granted security to hundreds and possibly thousands of items. Additionally they could be made members of one or more groups. Groups could also be granted access to the same items accessible to users. Any time a security option was granted or denied to a group or a user then the resulting access rights had to be modified for all group and or users impacted.

In addition to the item access, the system also allowed for modification of user or group properties. Because the application had only one SAVE button in the user interface it was important for the transaction to consist of either security modifications for one or more users, security and property modifications of a group or security and property modifications of a single user. In order to accomplish all these different tasks it would be necessary to use a DTC approach to provide the appropriate transaction level assuring that when the user clicked the SAVE button that all changes had indeed been saved.

Due to the overhead of DTC in this case, and the possible number of database calls that could be made when saving thousands of security settings it was determined that the overhead of DTC was too costly. In this case we used three master stored procedures passing all the necessary information for the three scenarios. Each of these stored procedures established a master transaction and then called other stored procedures that processed the individual components. Figure 1 demonstrates work flow for the different scenarios.

Scenario 1 - Modify User Properties and Security

 
  • Parse Security String Into Table
  • Begin Transaction
  • Save Properties
  • Save Security
  • Commit Transaction

    • Scenario 2 - Modify Group Properties and Security

     
  • Parse Security String Into Table
  • Build Temporary Table Of Users in Group
  • Open Cursor On Users
  • Begin Transaction
  • Save Security For Each User In Cursor
  • Save Group Properties
  • Commit Transaction

    • Scenario 3 - Modify Security for Multiple
    Users and/or Groups

     
  • Parse Security String Into Table
  • Build Temporary Table Of Users and User in Group(s)
  • Open Cursor On Users
  • Begin Transaction
  • Save Security For Each User In Cursor
  • Commit Transaction

    • Figure 1 - Work Flow For Modification Scenarios

    In order to utilize just one stored procedure as a master transaction coortinator it becomes necessary to pass n number of items as a parameter to a stored procedure. Our security example allows the user to assign zero to n items to a user or group. A list of Groups/Users and Item assignments must all be sent to the stored procedure. So, before we can address the transaction problem let's look at how to pass n Items to a stored procedure.

    How to Pass n Items to a Stored Procedure

    There are number of methods to pass n number of records to a stored procedure. I demonstrated one of these methods in my article Pass A Variable Number Of Parameters To Your Stored Procedures With A User Defined Function. In that article you will find a link to other sources that are more comprehensive regarding methods for passing multiple parameters to stored procedures. I don't wish to repeat their work. But I will include a couple here to provide a self encapsulated article.

    All of the master stored procedures in this article use multiple parameters. This allows the middle tier to pass to the stored procedure those items for which there is only one discrete value. One of the useful features of a stored procedure is that you can have an input parameter of the type TEXT or nTEXT. This allows for you to define an input parameter much larger than the VARCHAR(8000) or nVARCHAR(4000) normally allowed. You can not modify this parameter, but you can process it's contents. All data that has zero or more records is passed in a single parameter as either XML or a Delimited List.

    Pass XML As A Stored Procedure Parameter Variable

    This is the easiest method to manage an un-known number of records in a stored procedure. That is because three commands can convert the XML into a table which can be manipulated as needed. Figure 2 demonstrates  XML that can be passed as a parameter. Figure 3 demonstrates the SQL converting the text to a table. Figure 4 shows the converted XML as it is represented in SQL Server and how it can them be utilized in a Stored Procedure.

       
       
       

    Figure 2 - XML Passing Multiple Records To A Stored Procedure In A Single Parameter

    CREATE PROCEDURE spParseXML(@Parameter TEXT)
     
    AS
     
    DECLARE @idoc INT
     
    -- Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @ITEMS
    -- Execute a SELECT statement using OPENXML rowset provider.
    SELECT  *
    FROM     OPENXML (@idoc, '/SECITEMS/ITEM',3)
    WITH     (ID INT,
                   Status CHAR(1))
     
    EXEC sp_xml_removedocument @idoc

    Figure 3 - Stored Procedure Using XML as Input From a Parameter

    ID Status
    100 D
    101 G
    102 U

    Figure 4 - Results Of Stored Procedure Parsing an XML String Parameter

    This example is presented simply because it is easy to code. It is not necessarily the best method because of the overhead associated with XMLDOM. This is called as an out of process service with security, stability and overhead concerns. Used judiciously it works fine and it is usually pretty easy to build the input XML. Once you are done you have a table that can be processed in your stored procedures any way necessary.

    Pass a Delimited List In a Stored Procedure Parameter Variable

    Passing and processing a delimited list in a parameter is a little bit harder to code in SQL Server 2000 than XML because of TSQL string manipulating restrictions. With YUKON this would make a great CLR process or function. Let's review how you would use this method in SQL Server 2k. Figure 5 demonstrates the same data from the previous example passed as a delimited string. Figure 6 demonstrates code to parse the string. Figure 7 shows the result from the process.

    100=D,101=G,102=U

    Figure 5 - XML Passing Multiple Records To A Stored Procedure In A Single Parameter

    CREATE PROCEDURE spParseDelimited (@Items TEXT)
     
    AS
     
        SET NOCOUNT ON
     
        CREATE TABLE #ITEMS (
            ID             INT         NOT NULL,
            STATUS  CHAR(1) NOT NULL)
     
        DECLARE @CONTINUE CHAR(1)
        DECLARE @START_POS INT
        DECLARE @END_POS INT
        DECLARE @NEW_STRING VARCHAR(100)
        DECLARE @SQL VARCHAR(1000)
     
        SELECT @CONTINUE = 'T', @START_POS = 1
     
        WHILE @CONTINUE = 'T'
        BEGIN
            SET @END_POS = CHARINDEX(',', @Items, @START_POS)
       
            IF @END_POS = 0
            BEGIN
                SET @END_POS = DATALENGTH(@Items) + 1
                SET @CONTINUE = 'F'
            END
       
            SET @NEW_STRING = RTRIM(LTRIM(SUBSTRING(@Items,
            @START_POS, @END_POS - @START_POS)))
       
            IF @NEW_STRING <> ''
            BEGIN
                SET @SQL = 'INSERT INTO #ITEMS SELECT ' +
                REPLACE(@NEW_STRING, '=', ',''') + ''''
       
                EXEC (@SQL)
            END
     
            SET @START_POS = @END_POS + 1
        END
     
        SELECT * FROM #ITEMS
     
        SET NOCOUNT OFF
    GO

    Figure 6 - Stored Procedure Using Delimited List as Input From a Parameter

    ID Status
    100 D
    101 G
    102 U

    Figure 7 - Results Of Stored Procedure Parsing a Delimited List Parameter

    Create Stored Procedures as Sub-Routines

    I have three Master stored procedures that are passed the appropriate data to modify properties and or security for one or more users and/or groups. What is common to each Master stored procedure is the need to modify security for one or more users. Therefore a stored procedure that maintains security settings is a good candidate for a sub-stored procedure. In my database I retain a log of what security modifications have been made, who made the changes, and when the changes were made. Since I now have the security assignments in a table form I can save the history easily using simple TSQL queries. For this example let's call this stored procedure spUpdateSecurity. It has the following logic:

    • Write History Records where the status differs from the current status
    • Update Security Records Where Status is Different
    • Insert Any New Security Records

    A second common feature is the need to maintain properties for a user or a group. Since groups and users are in the same table I can create a shared stored procedure managing these properties. For this example let's call this stored procedure spUpdateUserGroupProperties. It has the following logic:

    • Write History Records for Properties Modified
    • Update Properties

    Call A Stored Procedure Within A Stored Procedure

    Now let's look at the three Master Stored Procedures and how they would call the sub-Stored Procedures.

    1) Update Security for multiple users and/or groups

    • Parse the Security Items List Into A Table
    • Create a temporary table for users.
    • Insert into the table a list of all users represented by selected users or members of groups selected
    • Open a cursor on the temporary Table
    • Begin a Transaction
    • For Each Row Call spUpdateSecurity...use the temp table created in the master database so it does not have to be created each time (instead of passing the string to spUpdateSecurity)
    • Commit the Transaction
    • Close and Deallocate the Cursor
    • Return Result of the Stored Procedure to Calling Program

    2) Update Security and Properties for a Single User

    • Parse the Security Items List Into A Table
    • Begin a Transaction
    • Call spUpdateSecurity for the designated User...use the temp table created in the master Stored Procedure because you wrote your sub-procedure to expect this when processing multiple users for the same data
    • Call spUpdateUserGroupProperties for the designated user passing the properties as parameters in the sub-stored procedure
    • Commit the Transaction
    • Return Result of the Stored Procedure to Calling Program

    3) Update Security and Properties for a Group User

    • Parse the Security Items List Into A Table
    • Create a temporary table for users
    • Insert into the table a list of all users represented by members of the group being modified
    • Open a cursor on the temporary Table
    • Begin a Transaction
    • For Each Row Call spUpdateSecurity...use the temp table created in the master database so it does not have to be created each time (instead of passing the string to spUpdateSecurity)
    • Call spUpdateUserGroupProperties for the designated Group passing the properties as parameters in the sub-stored procedure
    • Commit the Transaction
    • Return Result of the Stored Procedure to Calling Program

    So what does this look like syntactically? Let's look at the prototype for spUpdateUserGroupProperties and then make a call to it.

    Prototype spUpdateUserGroupProperties:

    CREATE PROCEDURE spUpdateUserGroupProperties (
        @USER_ID INT,
        @NAME VARCHAR(64),
        @DESCRIPTION VARCHAR(256),
        @EMAIL VARCHAR(256))

    Call spUpdateUserGroupProperties and spUpdateSecurity from within a stored procedure

    CREATE PROCEDURE spUpdateGroup (
        @USER_ID INT,
        @NAME VARCHAR(64),
        @DESCRIPTION VARCHAR(256),
        @EMAIL VARCHAR(256),
        @SECURITY_STRING TEXT,
        @RESULT INT OUTPUT)

    AS

        DECLARE @RETURN INT

        CREATE TABLE #SECURITY...
        CREATE TABLE #USERS

        ... INSERT INTO #USERS USERS FROM GROUP
        ... PARSE STRING INTO #SECURITY TABLE

        BEGIN TRAN

        EXEC @RETURN = spUpdateUserGroupProperties @USER_ID, @NAME,
             @DESCRIPTION, @EMAIL

        IF @RETURN <> 0
        BEGIN
            SET @RESULT = 1 -- Did not Update Group Properties
        END

        DECLARE C CURSOR FOR SELECT USER_ID FROM #USERS
        OPEN C
        FETCH NEXT FROM C INTO @USER_ID
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC @RETURN = spUpdateSecurity @USER_ID
       
            IF @RETURN <> 0
            BEGIN
                SET @RESULT = 2 -- Did not Update Users Security
                EXIT
            END
            FETCH NEXT FROM C INTO @USER_ID
        END

        IF @RESULT = 0 COMMIT

        RETURN @RESULT

    Conclusion

    You can see a lot of similarity in each of the transactions above. By sending all of the data to a master stored procedure you receive all of the benefits of a single call to the database, a local transaction, and high speed performance when modifying multiple records without multiple round trips to and from an external calling program. Using stored procedures that call stored procedures results in re-usable code. This provides the benefits for quicker development and reduces the risk of bugs. Normally this would be less flexible and extensible; but, since you have separated the actual work into smaller stored procedures called from a master stored procedure you have recovered some of your flexibility and extensibility.

    This method is more cumbersome to develop and should only be utilized in instances where high performance is required. When high performance is required I can promise this method will reduce blocking and deadlocks because your transactions are as big as they need to be and as fast as possible. There are other reasons to call stored procedures from within a stored procedure such as code re-use that are always appropriate.

    About the Author

    Ben, btaylor@sswug.org, has been working with Relational Databases since 1984 on multiple platforms in multiple languages. Most of the Relational Databases since 1983 were of the SQL variety. Since 1993 Ben has worked primarily with Microsoft SQL Server starting with SQL Server 4.21. He has designed SQL solutions working with both Transaction Databases and Data Warehouses. His Query and Database Optimization techniques are in great demand.

    Ben lives in Indianapolis, Indiana with his Wife Ruth, Daughter Hannah and Son Sammy.