CREATE PROCEDURE dbo.sp_ErrLogInsert (
@ErrorCode VARCHAR(5),
@Keys XML,
@TableName VARCHAR(50),
@Source VARCHAR(50) )
AS
BEGIN
DECLARE @ProductCode VARCHAR(6)
DECLARE @FeedDate VARCHAR(12)
DECLARE @TransactionId INT
DECLARE @TransactionSeq INT
IF @Source = 'Flat01'
IF @TableName IN ('TransactionMaster', 'Deduction', 'NameInvolvement')
BEGIN
/*
<Key>
<ProductCode>E&O</ProductCode>
<FeedDate>200911051127</FeedDate>
<TransactionId>25</TransactionId>
<TransactionSeq>1</TransactionSeq>
</Key>
*/
INSERT INTO Flat01ErrLog (
ProductCode,
FeedDate,
TransactionId,
Source,
ErrCode,
ErrMsg )
SELECT
row.value('(./ProductCode)[1]' , 'VARCHAR(6)') AS ProductCode,
row.value('(./FeedDate)[1]' , 'VARCHAR(12)') AS FeedDate,
row.value('(./TransactionId)[1]', 'INT') AS TransactionId,
'Flat01MasterWK' AS Source,
@ErrorCode AS ErrCode,
( SELECT ErrMsg FROM Flat01ErrCode WHERE ErrCode = @ErrorCode ) AS ErrMsg
FROM @Keys.nodes('/Key') AS T(row)
END
ELSE IF @TableName IN ('TransactionDetail', 'Deductible', 'Limit', 'Premium', 'TransactionExtDetail')
BEGIN
/*
<Key>
<ProductCode>E&O</ProductCode>
<FeedDate>200911051127</FeedDate>
<TransactionId>25</TransactionId>
<TransactionSeq>1</TransactionSeq>
<Section>MED</Section>
<Subsection>1MM</Subsection>
<LocationNumber>1</LocationNumber>
</Key>
*/
INSERT INTO Flat01ErrLog (
ProductCode,
FeedDate,
TransactionId,
Section,
Subsection,
LocationNumber,
Source,
ErrCode,
ErrMsg )
SELECT
row.value('(./ProductCode)[1]' , 'VARCHAR(6)') AS ProductCode,
row.value('(./FeedDate)[1]' , 'VARCHAR(12)') AS FeedDate,
row.value('(./TransactionId)[1]' , 'INT') AS TransactionId,
row.value('(./Section)[1]' , 'VARCHAR(3)') AS Section,
row.value('(./Subsection)[1]' , 'VARCHAR(3)') AS Subsection,
row.value('(./LocationNumber)[1]', 'INT') AS LocationNumber,
'Flat01DetailWK' AS Source,
@ErrorCode AS ErrCode,
( SELECT ErrMsg FROM Flat01ErrCode WHERE ErrCode = @ErrorCode ) AS ErrMsg
FROM @Keys.nodes('/Key') AS T(row)
END
END
GO