Saving a Sales Order Part 1

,

Introduction

This article tries to look into a common scenario of saving a sales order which, many of us might have done over a few hundred times. What is special about a Sales

Order Entry? Well, a Sales Order has a fixed header infrmation and a variable number

of item details. A Sales Order may have 3 lines, 10 lines or 500 lines. 

I have seen applications where a stored procedure is called to save the header information,

then another stored procedure is called to save each line details. So if the Sales

Order has 75 lines, a procedure is called 75 times to save each line. Though this

looks the easiest solution, it is not advisable to do so for performance reasons.

For best performance, it is advisable to call only a single procedure and only once

during any given operation. So what is the next option?

Some of the applications I have seen in the past were found to be passing line details

in a comma separated string. The application will create such a string and will

pass it to the stored procedure. the stored procedure will then parse the string,

run a loop and save each item. This works well, but not very efficient.

This approach has two major draw backs:

  1. SQL Server 2000 restricts VARCHAR to 8000 characters and NVARCHAR to 4000 characters.

    If the Sales Order has a large number of line items and each item has lots of information,

    8000 characters may not be enough to pass the entire data. (Note: SQL Server 2005

    supports upto 2^31 characters for VARCHAR and 2^30 characters for NVARCHAR).

  2. While saving the record, there needs to be a loop which parses the string and iterates

    over each line item. Each item has to be saved with an INSERT statement and the

    INSERT statement has to be fired N times, where N is the number of items in the

    Sales Order.

This article explains how to handle the above scenario by passing data in XML format.

Sample Data

For the purpose of this example, let us take a sample order with 3 line items. The

application will create an XML buffer as given below, containing the details of

the items entered by the user.

    1 <lineItems>

    2  

<item>

    3  

  <itemNumber>A001</itemNumber>

    4  

  <qty>10</qty>

    5  

  <rate>10.5</rate>

    6  

</item>

    7  

<item>

    8  

  <itemNumber>A002</itemNumber>

    9  

  <qty>20</qty>

   10  

  <rate>11</rate>

   11  

</item>

   12  

<item>

   13  

  <itemNumber>A003</itemNumber>

   14  

  <qty>30</qty>

   15  

  <rate>13</rate>

   16  

</item>

   17 </lineItems>

 Now let us have a look at the stored procedure which accepts the above data

and inserts it to the database.

    1 CREATE

  PROCEDURE [dbo].[SaveSalesOrder]

    2 (

    3     @OrderNumber VARCHAR(10),

    4     @OrderDate DATETIME,

    5     @CustomerNumber

VARCHAR(10),

    6     @LineItems text

    7 )

Parsing the XML string involves a 3 step process. The first step is to create a

handle that can be used to parse the XML buffer. 

   12 DECLARE

@hdoc INT   

   13 EXEC

sp_xml_preparedocument @hdoc OUTPUT, @LineItems

The next step is to use OPENXML() to access the data in the XML buffer as a table. 

   28 INSERT

INTO OrderDetails( OrderHeaderID, ItemNumber, Qty, Rate)

   29 SELECT

@OrderID, x.ItemNumber, x.Qty, x.Rate

   30    

FROM OPENXML ( @hdoc, '/lineItems/item',

2 ) WITH (

   31        

ItemNumber VARCHAR(20)

'itemNumber',

   32        

Qty INT 'qty',

   33        

Rate FLOAT 'rate'

   34     )

AS x

In the above code, the function OPENXML() returns a table containing the data we

passed in the XML buffer and we can directly insert it to the order detail table.

The next step is to release the XML handle we created earlier.

   37 EXEC

sp_xml_removedocument @hdoc

The above statement releases the XML handle. It is very important that you release

the XML handle. If you forget to do it, you will run out of system resources if

the stored procedure is called repeatedly.

Source listing

A complete listing of the source code is given below:

Sample Tables

Order Header

Order Details

    1 SET

ANSI_NULLS ON

    2 GO

    3 SET QUOTED_IDENTIFIER

ON

    4 GO

    5 SET

ANSI_PADDING ON

    6 GO

    7 CREATE

TABLE [dbo].[OrderHeader](

    8     [OrderID] [int]

IDENTITY(1,1) NOT NULL,

    9     [OrderNumber]

[varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL,

   10     [OrderDate] [datetime]

NULL,

   11     [CustomerNumber]

[varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

   12 ) ON

[PRIMARY]

   13 

   14 GO

   15 

   16 CREATE

TABLE [dbo].[OrderDetails](

   17     [OrderDetailID]

[int] IDENTITY(1,1) NOT NULL,

   18     [OrderHeaderID]

[int] NULL,

   19     [ItemNumber] [varchar](20)

COLLATE SQL_Latin1_General_CP1_CI_AS

NULL,

   20     [Qty] [int] NULL,

   21     [Rate] [float]

NULL

   22 ) ON

[PRIMARY]

   23 

   24 GO

   25 SET

ANSI_PADDING OFF

Stored Procedure

SaveSalesOrder

    1 CREATE

  PROCEDURE [dbo].[SaveSalesOrder]

    2 (

    3     @OrderNumber VARCHAR(10),

    4     @OrderDate DATETIME,

    5     @CustomerNumber

VARCHAR(10),

    6     @LineItems text

    7 )

    8 AS

    9 

   10 SET NOCOUNT

ON

   11 

   12 BEGIN

TRAN

   13 

   14 -- Initialize

XML handle

   15 DECLARE

@hdoc INT   

   16 EXEC

sp_xml_preparedocument @hdoc OUTPUT, @LineItems

   17 

   18 -- Insert

data to order header

   19 INSERT

INTO OrderHeader (OrderNumber, OrderDate, CustomerNumber)   

   20    

VALUES (@OrderNumber, @OrderDate, @CustomerNumber)

   21 

   22 -- Take

the IDENTITY of the new record

   23 DECLARE

@OrderID INT

   24 SET

@OrderID = SCOPE_IDENTITY()

   25 

   26 -- Insert

data to Order Details

   27 INSERT

INTO OrderDetails( OrderHeaderID, ItemNumber, Qty, Rate)

   28 SELECT

@OrderID, x.ItemNumber, x.Qty, x.Rate

   29    

FROM OPENXML ( @hdoc, '/lineItems/item',

2 ) WITH (

   30        

ItemNumber VARCHAR(20)

'itemNumber',

   31        

Qty INT 'qty',

   32        

Rate FLOAT 'rate'

   33     )

AS x

   34 

   35 -- Release

XML handle

   36 EXEC

sp_xml_removedocument @hdoc

   37 

   38 IF

@@ERROR <> 0

   39    

ROLLBACK TRAN

   40 ELSE

   41    

COMMIT TRAN

Calling the procedure

Download the procedure

    1 EXECUTE

dbo.SaveSalesOrder '100001',

'01-01-2007', 'JAC001',

    2 '<lineItems>

    3  

<item>

    4    

<itemNumber>A001</itemNumber>

    5    

<qty>10</qty>

    6    

<rate>10.5</rate>

    7  

</item>

    8  

<item>

    9    

<itemNumber>A002</itemNumber>

   10    

<qty>20</qty>

   11    

<rate>11</rate>

   12  

</item>

   13  

<item>

   14    

<itemNumber>A003</itemNumber>

   15    

<qty>30</qty>

   16    

<rate>13</rate>

   17  

</item>

   18 </lineItems>'

Conclusions

We just saw how to process an XML buffer in TSQL and access the data elements. The

next few articles will discuss more complex XML processing and the new XML data

type introduced by SQL Server 2005.

Rate

4.67 (3)

Share

Share

Rate

4.67 (3)