Inserting parent and multiple child rows with TRAN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Inserting parent and multiple child rows with TRAN

Author

Topic

Disco Patrick
Starting Member

18 Posts

Posted - 2009-05-29 : 05:24:39

Hi all,

I'm just getting into stored procedures as I thought they would be a good way of solving my problem - which is this:

Lets say I have a table CUSTOMERORDER and a table ORDERITEM. When the user clicks a button 'Submit Order' on my website, I want to insert a new CUSTOMERORDER , and then insert the ORDERITEMS belonging to it. The problem is, if any of the ORDERITEMs fail to insert, then I'm left with an incomplete order in the database, which I don't like.

I would prefer to be able to roll back the changes, so that the CUSTOMERORDER is not put into the database... hmm, this could result in a very annoyed customer who has to go back and create their order all over again - but I'll worry about that later

So I'm using a transaction to do this. First I insert the CUSTOMERORDER. If there are any errors, I rollback the changes, and GOTO some SQL that outputs an error message. If there are no errors, I continue.

Next I want to insert the new ORDERITEMS, and again, if there are any errors, roll back the changes so that the CUSTOMERORDER and all ORDERITEMS inserted thus far are removed. Then GOTO the same error message SQL.

The thing is, how do I get my stored proc to iterate through a list of ORDERITEMS? How do I pass this list to the stored proc? Is it possible to pass a collection of some kind to a stored proc? If not, then how else would I achieve what I want?

Disco Patrick
Starting Member

18 Posts

Posted - 2009-05-29 : 05:30:48

I think I may have found the answer to my question on this page:
http://www.primaryobjects.com/CMS/Article79.aspx

...but if anyone has any other tips feel free to let me know!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 05:31:04

You can send the complete order as XML to the stored procedure, and have the SP do all the work for you (inlcuding transaction handling).

E 12°55'05.63"
N 56°04'39.26"

Disco Patrick
Starting Member

18 Posts

Posted - 2009-05-29 : 05:39:12

That sounds good.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 06:28:57

See http://www.sommarskog.se/arrays-in-sql-2005.html#XML

E 12°55'05.63"
N 56°04'39.26"

Disco Patrick
Starting Member

18 Posts

Posted - 2009-05-29 : 07:42:38

Started off by using the OPENXML method but read that it was less efficient than using .nodes so am working with that now.

Can't seem to select anything from my XML doc at the moment though. Can you give me an example of how to select a number of nodes and their attributes from an xmldoc?


-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Me
-- Create date: 
-- Description:
-- =============================================
ALTER PROCEDURE smstool_Insert 
	-- Add the parameters for the stored procedure here
AS
BEGIN TRANSACTION
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @XmlDoc xml
	--DECLARE @intDoc int

    -- Insert statements for procedure here
	
	SET @XmlDoc = N'
	<?xml version="1.0" encoding="utf-8" ?>
	<cA cID="1" createdBy="xxx-xxx" mID="1">
		<rTA rTID="1" sLID="27" lLID="1" />
		<rTA rTID="2" sLID="2" lLID="1" />
		
	</cA>'

	--Load the XML doc into memory
	--EXEC sp_xml_preparedocument @intDoc OUTPUT, @XmlDoc

	SELECT *
	FROM @XmlDoc.nodes('cA')
	AS MyTable
--	WITH			(CID int '@cID'
--						,CreatedBy uniqueidentifier '@createdBy'
--						,MID int '@mID')


GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 08:30:12

http://weblogs.sqlteam.com/peterl/archive/2008/09/01/Updated-XML-search-test-case-with-variables.aspx
http://weblogs.sqlteam.com/peterl/archive/2008/03/26/Some-XML-search-approaches.aspx

E 12°55'05.63"
N 56°04'39.26"

Disco Patrick
Starting Member

18 Posts

Posted - 2009-05-29 : 09:05:39

In the links you gave above, where does 'cust.coldef.value' come from?

Disco Patrick
Starting Member

18 Posts

Posted - 2009-05-29 : 09:32:03

Ok, I'm reverting back to the OPENXML method as at least I can get that returning a results set - even if it is supposedly not as efficient as using the newer method.

So I've got a result set that I now want to convert into actual rows in a table. How do I do this? Perhaps this is a question for another thread...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources