Advanced Search

Return to Snippet

Revision: 70193
at December 9, 2015 22:41 by cjb_bjc
Initial Code 
CREATE PROCEDURE dbo.OrderSave
@Order as XML
AS
BEGIN
BEGIN TRY
	-- Add the Order object into a temp table.
	SELECT 
		Order.value('OrderId[1]', 'INT') As OrderId,
		Order.value('OrderItemId[1]', 'INT') AS OrderItemId,
		Order.value('SegmentId[1]', 'INT') As SegmentId,
		Order.value('MessageType[1]', 'INT') As MessageType,
		Order.value('JobId[1]', 'INT') AS JobId,
		Order.value('CreatedDateTime[1]', 'DATETIME') AS CreatedDateTime
	INTO #TempOrder
	FROM @Order.nodes('//Order') T1(Order);

	BEGIN TRANSACTION
		MERGE Orders as Target
		USING
		(
		SELECT OrderId, OrderItemId, SegmentId, MessageType, JobId, CreatedDateTime From Orders
		)
		AS Source (OrderId, OrderItemId, SegmentId, MessageType, JobId, CreatedDateTime)
		ON Target.OrderId = Source.OrderId
		WHEN MATCHED THEN
			UPDATE SET JobId = source.JobId, CreatedDateTime = source.CreatedDateTime
		WHEN NOT MATCHED THEN
			INSERT (OrderItemId, SegmentId, MessageType)
			VALUES (source.OrderItemId, source.SegmentId, source.MessageType)
	COMMIT

	-- Now Drop the temp table.
	DROP TABLE #TempOrder;
	
	END TRY
	BEGIN CATCH   
	DECLARE @error_message VARCHAR(8000); SET @error_message = CASE WHEN error_number() < 50000 THEN error_message() + ' [in proc ' + error_PROCEDURE() + ' at line ' + CONVERT(VARCHAR, error_line()) + ']' ELSE error_message() END; -- user-generated can be shown to user
	DECLARE @error_state INT; SET @error_state = CASE WHEN error_number() < 50000 THEN 1 ELSE error_state() END; -- system-generated             

	IF @@trancount > 0 ROLLBACK TRAN; -- if there's an open transaction, roll it back
	RAISERROR(@error_message, 16, @error_state); -- error_state: 1 (system-generated), 2 (user-generated errors), 3 (user-generated warnings), 4 (user-generated info), 5 (user-generated question)

	RETURN error_number();
END CATCH
END
GO
Initial URL 
www.designpuddle.com
Initial Description 
PARSE XML OBJECT TO TEMP TABLE AND THEN MERGE
Initial Title 
PARSE XML OBJECT TO TEMP TABLE AND THEN MERGE
Initial Tags 

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
SQL