SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Saving a Sales Order Part 1


Saving a Sales Order Part 1

Author
Message
Amara Mustapha
Amara Mustapha
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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


sholliday
sholliday
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 183

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






jacob sebastian
jacob sebastian
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2648 Visits: 2523

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.



.
yazalpizar_
yazalpizar_
Old Hand
Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)

Group: General Forum Members
Points: 310 Visits: 626
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search