Maintaining Information

Congratulations to Viktor Tishchenko, a DBA for the Managed Health Care Association in Florham Park, New Jersey, and G. Jeffrey Dulian, a senior application developer for CIMCO Communications, Inc. in Oakbrook Terrace, Illinois. Viktor won first prize of $100 for the best solution to the December Reader Challenge, "Maintaining Information." Jeffrey won second prize of $50. Here's a recap of the problem and the solution to the December Reader Challenge.

Problem:

Alan is a programmer for a company that hosts several SQL Server 2000 data warehouses. He has a stored procedure that executes as part of a SQL Server Agent T-SQL job. The stored procedure performs a series of operations that execute other stored procedures or data-manipulation statements in a particular order. SQL Server performs, within one transaction, all the changes the stored procedure makes. In case of errors raised from code via the T-SQL RAISEERROR statement, the entire transaction gets rolled back by the database engine on the server.

Alan wants to maintain status information for each stored procedure executed, no matter what the outcome of the transaction. He wants to record the information in a table at the end of the transaction. Assume that he plans to store the status information in a table that has the following structure:

CREATE TABLE StatusInfo (
    RunID int NOT NULL,
    Stage varchar(50) NOT NULL,
    StartTime datetime NOT NULL,
    EndTime datetime NOT NULL,
    Status tinyint NOT NULL
)

Help Alan implement the logic to maintain the status information irrespective of the transaction's outcome.

Solution:

Alan can use SQL Server 2000's table variables feature to store the transient status information. The advantage of table variables in this scenario is that they don't participate in user-defined transactions, so the transaction's outcome doesn't affect them. By using a table variable to store the intermediate results, Alan can insert the results into the StatusInfo table at the end of the transaction after it commits or rolls back.

The following sample batch demonstrates the use of a table variable to store the stage details and retrieve them after the transaction rolls back. Note that this approach won't work for fatal error conditions that abort the entire batch.

DECLARE @StatusInfo TABLE(
    RunID int NOT NULL,
    Stage varchar(10) NOT NULL,
    StartTime datetime NOT NULL DEFAULT current_timestamp,
    EndTime datetime NULL,
    Status tinyint NULL
)
DECLARE @retcode int
BEGIN TRAN

-- Record initial status for first stored procedure call.
INSERT INTO @StatusInfo (RunID, Stage) VALUES( 1, 'SP1' )
EXEC @retcode = sp_who2 1
IF @retcode|@@error  0
BEGIN
    IF @@trancount > 0 rollback

    UPDATE @StatusInfo
       SET EndTime = current_timestamp,
           Status = 1
     WHERE RunID = 1
       AND Stage = 'SP1'
    GOTO end_batch
END

-- Record successful outcome of stored procedure call.
UPDATE @StatusInfo
   SET EndTime = current_timestamp,
       Status = 0
 WHERE RunID = 1
   AND Stage = 'SP1'

INSERT INTO @StatusInfo (RunID, Stage) VALUES( 1, 'SP2' )
EXEC @retcode = sp_who2 2
IF 1=1                   --Simulate error here.
BEGIN
    IF @@trancount > 0 rollback

-- Record error condition of stored procedure call.
UPDATE @StatusInfo
       SET EndTime = current_timestamp,
           Status = 1
     WHERE RunID = 1
       AND Stage = 'SP1'
    GOTO end_batch
END

-- Record successful outcome of stored procedure call.
UPDATE @StatusInfo
   SET EndTime = current_timestamp,
       Status = 0
 WHERE RunID = 1
   AND Stage = 'SP1'
COMMIT

end_batch:

-- Get the accumulated status details:
SELECT * FROM @StatusInfo

DECEMBER READER CHALLENGE:

Now, test your SQL Server savvy in the January Reader Challenge, "Inserting Order Details" (below). Submit your solution in an email message to [email protected] by December 18. 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.

Rick is a database developer for a company that sells products online. SQL Server 2000 hosts the online transaction-processing (OLTP) database and customers place their orders through a Web service application that the company created. The OLTP database schema is similar to the Northwind sample database, and order information is similar to data in the Orders and OrderDetails tables. Help Rick write a stored procedure that can be called by the Web services application to insert an order row, with details, into the database. The stored procedure needs to:

• Take order details in XML format
• Return the order ID that the processing system generates
• Validate the XML that the Web services application submits
• Enable systems that produce the XML (in two formats) to use the stored procedure.

The following XML shows two valid fragments, one with shipping information and one without shipping information.

<!-- Without shipping information -->
<root>
   <Order CustomerID="PARIS" EmployeeID="1" OrderDate="2003-08-04T00:00:00" 
RequiredDate="2003-08-04T00:00:00">
      <OrderDetail ProductID="61" UnitPrice="28.5" Quantity="12" Discount=""/>
      <OrderDetail ProductID="62" UnitPrice="49.3" Quantity="7" Discount="0.07"/>
   </Order>
</root>

<!-- With shipping information -->
<root>
   <Order CustomerID="PARIS" EmployeeID="1" OrderDate="2003-08-04T00:00:00" 
RequiredDate="2003-08-04T00:00:00"
ShipCity="Seattle" ShipCountry="USA">
      <OrderDetail ProductID="11" UnitPrice="22.000" Quantity="12" Discount=""/>
      <OrderDetail ProductID="42" UnitPrice="56.000" Quantity="7" Discount="0.07"/>
   </Order>
</root>