Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Saving a Sales Order Part 1

By Jacob Sebastian,

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.

Total article views: 12009 | Views in the last 30 days: 19
 
Related Articles
FORUM

Dynamic Order by -need both Dates and Varchars

Ordering with both Dates and Varchars

FORUM

Stored Procedure with varchar(max) as parameter

Stored Procedure with varchar(max) as parameter

ARTICLE

Sales Order Workshop Part III

In the previous articles, Jacob Sebastian looked at using XML to save a sales order with variable nu...

SCRIPT

Random varchars

This procedure generates a list of random varchars (8 charactes length, only letters - lower or uppe...

FORUM

varchar issue

varchar issue

Tags
miscellaneous    
stored procedures    
t-sql    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones