IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID = OBJECT_ID('dbo.USPGNOVAAnalyseXML') AND sysstat & 0xf = 4)
drop procedure dbo.USPGNOVAAnalyseXML
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
GO
CREATE PROCEDURE dbo.USPGNOVAAnalyseXML
( @cTRANDATE CHAR(10),
@request_no VARCHAR(30),
@staff_code VARCHAR(10),
@cUSERID CHAR(8),
@cCALLTYPE CHAR(1), -- O - Online, B -- Batch
@cRETCODE CHAR(4) OUTPUT,
@cRETMESSAGE NVARCHAR(MAX) OUTPUT
) AS
/*******************************************************************
COMPASS 2000 USER STORED PROCEDURE
USPGNOVAAnalyseXML.SQL - Analyse XML to dispatch the record
PROCESSING DETAILS:
Analyse XML to dispatch the record
AUTHOR : Keith He
DATE : 12/12/2014
PIRNO :
REVISION LOG:
VERSION PIRNO PROGRAMMER REMARK DATE PURPOSE
5.0 NOVA Keith He 12/12/2014 Initial Version
********************************************************************/
/*error handling variable section */
BEGIN
DECLARE @ErrFrom CHAR(20)
DECLARE @cErrorMsg varchar(3000)
DECLARE @ErrData CHAR(45)
DECLARE @ErrCode INT
DECLARE @StoreProcInd CHAR(1)
DECLARE @cMSGID CHAR(5)
/*DECLARE VARIABLES AND CONSTANTS*/
DECLARE @cACTIVE CHAR(1)
DECLARE @cDELETED CHAR(1)
DECLARE @cYES CHAR(1)
DECLARE @cNO CHAR(1)
DECLARE @cProgramID VARCHAR(60)
DECLARE @form_id decimal(9,0)
DECLARE @cERRORCODE CHAR(4)
DECLARE @nCurCount INT
DECLARE @nTotalCount INT
DECLARE @cTableName VARCHAR(20)
DECLARE @xmlRecord XML
DECLARE @nOrder_Idx INT
DECLARE @cExitSQL NVARCHAR(MAX)
DECLARE @nExist INT
DECLARE @nIdx INT
DECLARE @cSqlStatement NVARCHAR(MAX)
SELECT @cACTIVE = 'A' , @cRETCODE = '0000', @cProgramID = 'dbo.USPGNOVAAnalyseXML'
declare @XML XML
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
create table #temp1
(
order_idx int null,
tablename varchar(20) null
)
create table #temprecorxml
(
idx int identity(1,1),
order_idx int null,
tablename varchar(20) null,
recordxml xml null,
sqlstatement nvarchar(max) null
)
INSERT T_Nova_ErrlogTran (RequestNo,ProgramID,ERRORLOG,RCDUSRID,RCDDTSTMP)
VALUES(@request_no,@cProgramID,'Analyse XML Begin',@cUSERID,GETDATE())
--Retrieve the form XML
SELECT @XML = NovaXML FROM T_Nova_TXMLConvert where request_no = @request_no --'NBForm_11112014_02'
--Retrieve the form ID from XML
select @form_id = T.N.value('@form_system_id','decimal(9,0)') From @XML.nodes('form') T(N)
SELECT @cERRORCODE = RTRIM(error_code) FROM T_Nova_CompassProcess WHERE process_sp = 'USPGNOVAAnalyseXML' AND (form_system_id = @form_id OR form_system_id =0)
--Retrieve distinct table name from XML in sequence
select @nTotalCount = @XML.value('count(/form/*)','int')
select @nCurCount = 0
while @nCurCount <= @nTotalCount
begin
select @cTableName = T.a.value('upper-case(local-name(.))','varchar(20)')
from @XML.nodes('/form/*[position()=sql:variable("@nCurCount")]') T(a)
insert into #temp1 (order_idx, tablename) values(@nCurCount, @cTableName)
SELECT @ErrCode = @@error
IF @ErrCode <> 0
BEGIN
SELECT @cRETCODE = @cERRORCODE
INSERT T_Nova_ErrlogTran (RequestNo,ProgramID,ERRORLOG,RCDUSRID,RCDDTSTMP)
VALUES(@request_no,@cProgramID,'insert temp table #temp1 error',@cUSERID,GETDATE())
GOTO EXIT_WINDOW
END
select @nCurCount = @nCurCount + 1
end
--Separate XML into record level
insert into #temprecorxml(order_idx, tablename, recordxml)
select t1.order_idx, t1.TABLENAME, convert(XML, '<form><' + t1.TABLENAME + '>' + convert(nvarchar(max), t.a.query('.')) + '</' + t1.TABLENAME + '></form>') as recordxml
--into #temprecorxml
from #temp1 t1
cross apply
@XML.nodes('/form/*[upper-case(local-name(.))=sql:column("t1.TABLENAME")]/item') t(a)
--Loop record XML
declare cur_recordxml cursor for
select t1.idx, t1.order_idx, t1.tablename, t1.recordxml
from #temprecorxml T1
order by t1.order_idx, t1.idx
open cur_recordxml
fetch next from cur_recordxml into @nIdx, @nOrder_Idx, @cTableName, @xmlRecord
while @@fetch_status = 0
begin
select @nExist = 0
if exists (select 1 from sys.sysobjects where xtype = 'PK' and parent_obj = object_id(@cTableName))
begin
select @cExitSQL = 'select @nExist = 1' + char(13) + 'from ' + @cTableName + ' t1' + char(13) +
'cross apply @xmlJudge.nodes(''/form/*[upper-case(local-name(.))=sql:variable("@cTableNameJudge")]/item'') Tbl(Col)' + char(13) +
'where ' +
stuff((select ',t1.' + t3.name + ' = ' + 'Tbl.Col.value(''' + t3.name + '[1]''' + '|!|''' +
(CASE
WHEN t4.name IN ('NCHAR','NVARCHAR') then t4.name + '(' + IIF(t3.max_length = -1,'MAX',CAST(t3.max_length/2 AS VARCHAR)) + ')'
WHEN t4.name IN ('CHAR','VARCHAR') then t4.name + '(' + IIF(t3.max_length = -1,'MAX',CAST(t3.max_length AS VARCHAR)) + ')'
WHEN t4.name = 'DECIMAL' then t4.name + '(' + CAST(t3.[precision] AS varchar) + ',' + CAST(t3.[scale] AS varchar) + ')'
ELSE t4.name END
) + ''')'
from sys.sysobjects t0
INNER JOIN sys.sysindexes t1
ON t0.name = t1.name
INNER JOIN sys.sysindexkeys t2
ON t2.indid = t1.indid
INNER JOIN SYS.COLUMNS t3
ON t3.OBJECT_ID = OBJECT_ID(@cTableName)
and t3.name = col_name(object_id(@cTableName), t2.colid)
INNER JOIN SYS.TYPES t4
ON t4.system_type_id = T3.system_type_id
and t4.user_type_id = T3.user_type_id
cross apply @xmlRecord.nodes('/form/*[upper-case(local-name(.))=sql:variable("@cTableName")]/item/*[upper-case(local-name(.))=upper-case(sql:column("t3.name"))]') Tbl(Col)
where t0.xtype = 'PK'
and t0.parent_obj = object_id(@cTableName)
and t2.id = object_id(@cTableName)
for xml path('')
),1,1,''
)
select @cExitSQL = replace(@cExitSQL, ',', ' and ')
select @cExitSQL = replace(@cExitSQL, '|!|', ',')
select @cExitSQL = replace(@cExitSQL, '4 and 0', ' 4,0 ')
select @cExitSQL = 'declare @xmlJudge xml, @cTableNameJudge varchar(20);' + CHAR(13) +
'SELECT @xmlJudge = convert(xml, ''' + convert(nvarchar(max), @xmlRecord) + ''');' + CHAR(13) +
'SELECT @cTableNameJudge = ''' + @cTableName + ''';' + CHAR(13) +
@cExitSQL
begin try
exec sp_executesql @cExitSQL, N'@nExist int output', @nExist output
end try
begin catch
SELECT @ErrCode = @@error
IF @ErrCode <> 0
BEGIN
SELECT @cRETCODE = @cERRORCODE
INSERT T_Nova_ErrlogTran (RequestNo,ProgramID,ERRORLOG,RCDUSRID,RCDDTSTMP)
VALUES(@request_no,@cProgramID,'judge record existence error',@cUSERID,GETDATE())
close cur_recordxml
deallocate cur_recordxml
GOTO EXIT_WINDOW
END
end catch
end
--If not exist, call insert logic, else call update logic
if @nExist = 1
begin
--call update logic
exec UspGNOVAPrepareUpdateStatement @cTRANDATE, @request_no, @xmlRecord, @cUSERID, @cCALLTYPE, @form_id, @cRETCODE output, @cSqlStatement output
if @cRETCODE <> '0000'
begin
INSERT T_Nova_ErrlogTran (RequestNo,ProgramID,ERRORLOG,RCDUSRID,RCDDTSTMP)
VALUES(@request_no,@cProgramID,'calling prepare update statement SP error',@cUSERID,GETDATE())
close cur_recordxml
deallocate cur_recordxml
GOTO EXIT_WINDOW
end
end
else
begin
--call insert logic
exec UspGNOVAPrepareInsertStatement @cTRANDATE, @request_no, @xmlRecord, @cUSERID, @cCALLTYPE, @form_id, @cRETCODE output, @cSqlStatement output
if @cRETCODE <> '0000'
begin
INSERT T_Nova_ErrlogTran (RequestNo,ProgramID,ERRORLOG,RCDUSRID,RCDDTSTMP)
VALUES(@request_no,@cProgramID,'calling prepare insert statement SP error',@cUSERID,GETDATE())
close cur_recordxml
deallocate cur_recordxml
GOTO EXIT_WINDOW
end
end
--Update insert/update statement back to temp table
update #temprecorxml set sqlstatement = @cSqlStatement where idx = @nIdx and order_idx = @nOrder_Idx and tablename = @cTableName
SELECT @ErrCode = @@error
IF @ErrCode <> 0
BEGIN
SELECT @cRETCODE = @cERRORCODE
INSERT T_Nova_ErrlogTran (RequestNo,ProgramID,ERRORLOG,RCDUSRID,RCDDTSTMP)
VALUES(@request_no,@cProgramID,'update insert/update statement back to temp table error',@cUSERID,GETDATE())
close cur_recordxml
deallocate cur_recordxml
GOTO EXIT_WINDOW
END
fetch next from cur_recordxml into @nIdx, @nOrder_Idx, @cTableName, @xmlRecord
end
close cur_recordxml
deallocate cur_recordxml
delete from T_Nova_InsertCheck where request_no = @request_no
insert into T_Nova_InsertCheck
select tableName,sqlstatement,@request_no as request_no,order_idx as orderby from #temprecorxml
drop table #temp1
--select * from #temprecorxml
INSERT T_Nova_ErrlogTran (RequestNo,ProgramID,ERRORLOG,RCDUSRID,RCDDTSTMP)
VALUES(@request_no,@cProgramID,'start processing insert/update script',@cUSERID,GETDATE())
begin try
begin tran
If Cursor_Status('global','cur_sqlstatement')>=-1
BEGIN
DEALLOCATE cur_sqlstatement
End
declare cur_sqlstatement cursor for
select sqlstatement from #temprecorxml where sqlstatement is not null order by order_idx, idx
open cur_sqlstatement
FETCH NEXT FROM cur_sqlstatement INTO @cSqlStatement
WHILE (@@FETCH_STATUS = 0)
BEGIN
exec sp_executesql @cSqlStatement
FETCH NEXT FROM cur_sqlstatement INTO @cSqlStatement
END
CLOSE cur_sqlstatement
DEALLOCATE cur_sqlstatement
INSERT T_Nova_ErrlogTran (RequestNo,ProgramID,ERRORLOG,RCDUSRID,RCDDTSTMP)
VALUES(@request_no,@cProgramID,'start insert sub table script',@cUSERID,GETDATE())
If Cursor_Status('global','cur_insertSubTable')>=-1
BEGIN
DEALLOCATE cur_insertSubTable
End
declare cur_insertSubTable cursor for
select VALUEUSAGE from TSYSPARMH where PARMDESC='NOVASP'
AND PARMTYPE = CASE WHEN (SELECT DISTINCT 1 FROM TSYSPARMH WHERE PARMDESC='NOVASP' AND PARMTYPE=CONVERT(VARCHAR(9),@form_id)) = 1 THEN CONVERT(VARCHAR(9),@form_id) ELSE '0' END
ORDER BY PARMVALUE
open cur_insertSubTable
FETCH NEXT FROM cur_insertSubTable INTO @cSqlStatement
WHILE (@@FETCH_STATUS = 0)
BEGIN
set @cSqlStatement = Cast('exec ' + @cSqlStatement + ' ''' + @request_no + '''' as nvarchar(300))
exec sp_executesql @cSqlStatement
FETCH NEXT FROM cur_insertSubTable INTO @cSqlStatement
END
CLOSE cur_insertSubTable
DEALLOCATE cur_insertSubTable
commit tran
end try
begin catch
if @@trancount > 0
rollback tran
If Cursor_Status('global','cur_insertTable')>=-1
BEGIN
DEALLOCATE cur_insertTable
End
If Cursor_Status('global','cur_insertSubTable')>=-1
BEGIN
DEALLOCATE cur_insertSubTable
End
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
SELECT @cRETCODE = @cERRORCODE
Set @cErrorMsg = @ErrorMessage
INSERT T_Nova_ErrlogTran (RequestNo,ProgramID,ERRORLOG,RCDUSRID,RCDDTSTMP)
VALUES(@request_no,@cProgramID,@cErrorMsg+char(13)+'*****'+ char(13)+ @cSqlStatement,@cUSERID,GETDATE())
GOTO EXIT_WINDOW
end catch
INSERT T_Nova_ErrlogTran (RequestNo,ProgramID,ERRORLOG,RCDUSRID,RCDDTSTMP)
VALUES(@request_no,@cProgramID,'Analyse XML Successful',@cUSERID,GETDATE())
drop table #temprecorxml
EXIT_WINDOW:
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET ANSI_WARNINGS OFF
SET ANSI_PADDING OFF
RETURN
END
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET ANSI_WARNINGS OFF
SET ANSI_PADDING OFF
GO
浙公网安备 33010602011771号