SQL经典代码_03

Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.USPGNOVAPrepareUpdateStatement
(    @cTRANDATE        CHAR(10), 
    @request_no        VARCHAR(30),
    @xmlRecord        XML,
    @cUSERID        CHAR(8),
    @cCALLTYPE        CHAR(1),     --    O - Online, B -- Batch
    @form_id        decimal(9,0),
    @cRETCODE        CHAR(4) OUTPUT,
    @cSqlStatement    NVARCHAR(MAX) OUTPUT
) AS
/*******************************************************************
    COMPASS 2000 USER STORED PROCEDURE

    USPGNOVAPrepareUpdateStatement.SQL - Prepare insert statement for each record
        
        PROCESSING DETAILS:
        Prepare insert statement for each record
            
    AUTHOR      :     Keith He
    DATE        :     12/17/2014
    PIRNO        :      

    REVISION LOG:
    VERSION      PIRNO        PROGRAMMER    REMARK        DATE        PURPOSE
        5.0      NOVA        Keith He                12/17/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    @cERRORCODE    CHAR(4)
DECLARE @cTableName    VARCHAR(20)
DECLARE @cUpdateSQL        NVARCHAR(MAX)
DECLARE @cUpdateCONT    NVARCHAR(MAX)

SELECT @cACTIVE = 'A' , @cRETCODE = '0000', @cProgramID = 'dbo.USPGNOVAPrepareUpdateStatement'

SET ANSI_NULLS ON 
SET QUOTED_IDENTIFIER ON  
SET CONCAT_NULL_YIELDS_NULL ON 
SET ANSI_WARNINGS ON 
SET ANSI_PADDING ON 

SELECT @cERRORCODE = RTRIM(error_code)  FROM T_Nova_CompassProcess WHERE process_sp  = 'USPGNOVAAnalyseXML'


INSERT T_Nova_ErrlogTran (RequestNo,ProgramID,ERRORLOG,RCDUSRID,RCDDTSTMP) 
VALUES(@request_no,@cProgramID,'Prepare Update statement Begin',@cUSERID,GETDATE())

--Retrieve table name
select @cTableName = upper(T.N.value('local-name(.)','varchar(20)'))
from @xmlRecord.nodes('/form/*') T(N)

--Retrieve table field name
select DISTINCT @cTableName as TABLENAME,
    COLNAME2 = T2.N2.value('local-name(.)','nvarchar(20)')
into #temp2 
from @xmlRecord.nodes('/form/*[upper-case(local-name(.))=sql:variable("@cTableName")]/*/*') T2(N2)

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 #temp2 error',@cUSERID,GETDATE())
    
    GOTO EXIT_WINDOW
END    

if exists (select 1 from sys.sysobjects where xtype = 'PK' and parent_obj = object_id(@cTableName))
begin
    --Prepare update statement
    select @cUpdateSQL = 'update t1' + char(13) + 
                          'set ' + 
                            stuff((select ', t1.' + t4.COLNAME2 + ' = ' + 'NOVA.funXMLReplaceSpecNRevert(Tbl.Col.value(''' + t4.COLNAME2 + '[1]''' + '|!|''' + 
                                            (CASE WHEN t3.name IN ('NCHAR','NVARCHAR')    then t3.name + '(' + IIF(t2.max_length = -1,'MAX',CAST(t2.max_length/2 AS VARCHAR)) + ')' 
                                                WHEN t3.name IN ('CHAR','VARCHAR')        then t3.name + '(' + IIF(t2.max_length = -1,'MAX',CAST(t2.max_length AS VARCHAR)) + ')'
                                                WHEN t3.name = 'DECIMAL'                then t3.name + '(' + CAST(t2.[precision] AS varchar) + ',' + CAST(t2.[scale] AS varchar) + ')'
                                                ELSE t3.name END) + '''))'
                                    from SYS.COLUMNS T2 
                                    INNER JOIN SYS.TYPES T3 
                                        ON T2.system_type_id = T3.system_type_id  
                                        AND T2.user_type_id = T3.user_type_id  
                                        AND object_id(@cTableName) = T2.OBJECT_ID
                                    INNER JOIN #temp2 T4 
                                        ON t4.TABLENAME = @cTableName
                                        AND T2.NAME = t4.COLNAME2
                                    cross apply @xmlRecord.nodes('/form/*[upper-case(local-name(.))=sql:variable("@cTableName")]/item/*[upper-case(local-name(.))=upper-case(sql:column("t2.name"))]') Tbl(Col)
                                    for xml path('')
                                    ),1,1,''
                                  ) 
                          + char(13) +
                         'from ' + @cTableName + ' t1 ' + char(13) +
                         'cross apply @xml2.nodes(''/form/'+@cTableName+'/item'') Tbl(Col) '

    select @cUpdateSQL = replace(@cUpdateSQL, '|!|', ',')

    --Prepare update condition
    select @cUpdateCONT = 'where ' + 
                          stuff((select ' and t1.' + t3.name + ' = ' + 'NOVA.funXMLReplaceSpecNRevert(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,4,''
                                       )

    select @cUpdateCONT = replace(@cUpdateCONT, '', ' and ')
    select @cUpdateCONT = replace(@cUpdateCONT, '|!|', ',')

    IF (@cUpdateSQL is NULL) or (@cUpdateCONT is NULL)
    BEGIN
        SELECT @cRETCODE = @cERRORCODE
        select @cErrorMsg = 'generate script error for table: ' + @cTableName

        INSERT T_Nova_ErrlogTran (RequestNo,ProgramID,ERRORLOG,RCDUSRID,RCDDTSTMP) 
        VALUES(@request_no,@cProgramID,@cErrorMsg,@cUSERID,GETDATE())
    
        GOTO EXIT_WINDOW
    END

    select @cSqlStatement = N'declare @xml2 xml;' + CHAR(13) +
                            N'SELECT @xml2 = N''' + convert(nvarchar(max), @xmlRecord) + ''';' + CHAR(13) + 
                            + @cUpdateSQL + char(13) + @cUpdateCONT + ';'
end
else
begin
    SELECT @cRETCODE = '0001'
    
    INSERT T_Nova_ErrlogTran (RequestNo,ProgramID,ERRORLOG,RCDUSRID,RCDDTSTMP) 
    VALUES(@request_no,@cProgramID,'no primary key in table: ' + @cTableName,@cUSERID,GETDATE())
    
    GOTO EXIT_WINDOW
end

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