Wednesday, December 29, 2010

Passing Multiple Records to a Stored Procedure in SQL Server

CREATE PROCEDURE [ImportJobMaterials]
@JobMaterialsXml AS VARCHAR(MAX),
@JobID AS INT,
@ChangeOrderNumber AS VARCHAR(10) = NULL,
@ChangeOrderDescription AS VARCHAR(100) = NULL
AS
BEGIN
DECLARE @XML AS XML

DECLARE @MaterialsTable TABLE
(
ID INT IDENTITY(1,1),
Area VARCHAR(250),
Phase VARCHAR(250),
WorkCodeID INT,
WorkCodeTitle VARCHAR(250),
MaterialTitle VARCHAR(250),
Quantity DECIMAL(18,2),
TotalHours DECIMAL(18,2)
)

SELECT @XML = @JobMaterialsXml

INSERT INTO @MaterialsTable (Area, Phase, WorkCodeID, WorkCodeTitle, MaterialTitle, Quantity, TotalHours)
SELECT M.Item.query('./Area').value('.','VARCHAR(250)') Area,
M.Item.query('./Phase').value('.','VARCHAR(250)') WorkCode,
M.Item.query('./WorkCodeID').value('.','INT') WorkCodeID,
M.Item.query('./WorkCodeTitle').value('.','VARCHAR(250)') WorkCodeTitle,
M.Item.query('./MaterialTitle').value('.','VARCHAR(250)') MaterialTitle,
M.Item.query('./Quantity').value('.','DECIMAL(18,2)') Quantity,
M.Item.query('./TotalHours').value('.','DECIMAL(18,2)') TotalHours
FROM @XML.nodes('/ArrayOfJobMaterialImport/JobMaterialImport') AS M(Item)

--Process the data
END

No comments: