Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Saving a Sales Order Part 1 Expand / Collapse
Author
Message
Posted Tuesday, March 27, 2007 1:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 4, 2007 1:00 PM
Points: 4, Visits: 1

Thanks Paul

I was able to locate all the previous articles and I am going through them. They are very helpful. Thanks to all the experts out there for making DB life a little easier.

Amarado

Post #354310
Posted Tuesday, March 27, 2007 4:16 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 4, 2014 7:31 AM
Points: 117, Visits: 176

This is my bread and butter method now.  2000 = OPENXML.

2005 has some different xml functions.  Those should be explored.

..

One of the benefits is that indexes are updated after the entire set of data is inserted or updated.

Another is a "all or nothing" insert or update.  You don't orphan any data.

 

I've added a twist, where I can insert or update, based on the match of a primary key.

Here is an example:

 

 

if exists (select * from sysobjects
 where id = object_id('uspTitleUpdate') and sysstat & 0xf = 4)
 drop procedure uspTitleUpdate
GO


CREATE  PROCEDURE dbo.uspTitleUpdate (
 @xml_doc TEXT ,
 @numberRowsAffected int output  --return
)


AS


SET NOCOUNT ON


DECLARE @hdoc INT -- handle to XML doc


DECLARE @errorTracker int -- used to "remember" the @@ERROR


DECLARE @updateRowCount int
DECLARE @insertRowCount int


--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML_Doc


-- build a table (variable table) to store the xml-based result set
DECLARE @titleupdate TABLE (
 identityid int IDENTITY (1,1) ,


title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate datetime ,


--used to differeniate between existing (update) and new ones (insert)
alreadyExists bit DEFAULT 0
)


--the next call will take the info IN the @hdoc(with is the holder for
@xml_doc), and put it IN a variableTable
INSERT @titleupdate
 (
  title_id ,
  title ,
  type ,
  pub_id ,
  price ,
  advance ,
  royalty ,
  ytd_sales ,
  notes ,
  pubdate ,
  alreadyExists
)
SELECT
 title_id ,
 title ,
 type ,
 pub_id ,
 price ,
 advance ,
 royalty ,
 ytd_sales ,
 notes ,
 dbo.udf_convert_xml_date_to_datetime (pubdate) , --<<UDF to convert xmldate to tsql date
 0
FROM
 -- use the correct XPath .. the second arg ("2" here) distinquishes
 -- between textnode or an attribute, most times with
 --.NET typed datasets, its a "2"
 --This xpath MUST match the syntax of the DataSet
 OPENXML (@hdoc, '/TitlesDS/Titles', 2) WITH (


 title_id varchar(6) ,
 title varchar(80) ,
 type varchar(32) ,
 pub_id varchar(32) ,
 price money ,
 advance money ,
 royalty varchar(32) ,
 ytd_sales varchar(32) ,
 notes TEXT ,
 pubdate varchar(32) ,
 alreadyExists bit)

EXEC sp_xml_removedocument @hdoc


--select * from @titleupdate


--lets differeniate between existing (update) and new ones (insert)
Update @titleupdate
SET
 alreadyExists = 1
FROM
 @titleupdate tu , titles
WHERE
 --this where clause is a little weird, usually you'll must match
 --primary key (int or global identifiers)
 ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))


SET NOCOUNT OFF


Update
 titles
set
 title = tu.title ,
 type = tu.type ,
 pub_id = tu.pub_id ,
 price = tu.price ,
 advance  = tu.advance ,
 royalty  = tu.royalty ,
 ytd_sales  = tu.ytd_sales ,
 notes  = tu.notes ,
 pubdate  = tu.pubdate
FROM
 @titleupdate tu , titles
WHERE
 ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))
 AND
 tu.alreadyExists <> 0


Select @updateRowCount = @@ROWCOUNT


INSERT INTO titles
 (
  title_id ,
  title ,
  type ,
  pub_id ,
  price ,
  advance ,
  royalty ,
  ytd_sales ,
  notes ,
  pubdate
)
Select
 title_id ,
 title ,
 type ,
 pub_id ,
 price ,
 advance ,
 royalty ,
 ytd_sales ,
 notes ,
 pubdate
FROM
 @titleupdate
WHERE
 alreadyExists = 0


Select @insertRowCount = @@ROWCOUNT


select @numberRowsAffected = @insertRowCount + @updateRowCount


--select * from titles


SET NOCOUNT OFF


GO


 




Post #354346
Posted Tuesday, March 27, 2007 11:47 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523

Hi,

In part III of this series, I have presented the sample code which handles the insert/update/delete scenario. When a sales order is edited, a few new items may be added, a few items may be modified and/or a few items may be deleted. The sample code in Part III shows an example of how to handle it.



.
Post #354387
Posted Wednesday, December 21, 2011 12:30 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:37 AM
Points: 142, Visits: 469
Hi, very good series of articles, I'm starting from the first one, hope once reach the last one of the serie I can apply all I learned on MSSQL 2008R2 where I'm working on.

I found an issue, at least on this first part. The links of the source code does not lead to the correct place, no code is shown.
Post #1224918
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse