最近在优化一个存储过程，存储执行太慢，且没有用递归写法，我负责改成递归形式 @SourceSSNDataInfo是一个表变量 第0阶，之前是这么写的
INSERT INTO @SourceSSNDataInfo SELECT '00' AS 'Level',--Level '' AS 'ParentSysSSN',--ParentSysSSN '' AS 'ParentActualSSN',--ParentActualSSN '' AS 'ParentHHPN',--ParentHHPN '' AS 'ParentCustPN',--ParentCustPN --A.sysserialno AS 'SerialSSN', --SerialSSN --CASE WHEN LEN(LTRIM(RTRIM(B.ProductID))) <> 0 THEN B.ProductID ELSE A.sysserialno END AS 'ActualSSN ', --ActualSSN @strSSN AS 'SerialSSN',--SerialSSN A.sysserialno AS 'ActualSSN ',--ActualSSN CASE WHEN @virtualskuno = '' THEN A.skuno ELSE @virtualskuno END AS 'HHPN',--HHPN --A.custpartno AS 'CustPN', --CustPN C.partname AS 'CustPN',--CustPN A.categoryname AS 'CategoryName' FROM mfsysproduct AS A (NOLOCK), mfsysprodlink AS B (NOLOCK), mmprodmaster AS C (NOLOCK) WHERE A.sysserialno = @_strActualSSN AND A.sysserialno = B.SysSerialNo AND A.skuno = C.partno第1阶
INSERT INTO @SourceSSNDataInfo SELECT '01' AS 'Level',--Level A.SerialSSN AS 'ParentSysSSN',--ParentSysSSN A.ActualSSN AS 'ParentActualSSN',--ParentActualSSN A.HHPN AS 'ParentHHPN',--ParentHHPN A.CustPN AS 'ParentCustPN',--ParentCustPN --B.cserialno AS 'SerialSSN', --SerialSSN CASE WHEN B.cserialno = B.partno THEN A.SerialSSN WHEN B.cserialno = (SELECT Field2 FROM mfsysprodlink WHERE Field2 = B.cserialno AND ProductID <> '' AND cserialno LIKE '11S%') THEN (SELECT ProductID FROM mfsysprodlink WHERE Field2 = B.cserialno) ELSE B.cserialno END,--if CSN=PN else GET Top Level SSN info. --dbo.fn_GetSSNByInput(B.cserialno) AS 'ActualSSN ', --ActualSSN CASE WHEN dbo.Fn_getssnbyinput(B.cserialno) = A.ActualSSN THEN B.cserialno ELSE dbo.Fn_getssnbyinput(B.cserialno) END AS 'ActualSSN ',--ActualSSN Replace (B.partno, '-HH', '') AS 'HHPN',--HHPN --B.custpartno AS 'CustPN', --CustPN C.partname AS 'CustPN',--CustPN B.categoryname AS 'CategoryName' FROM @SourceSSNDataInfo AS A, mfsyscserial AS B (NOLOCK), mmprodmaster AS C (NOLOCK) WHERE A.ActualSSN = B.sysserialno AND A.[Level] = '00' AND B.categoryname NOT IN (SELECT Value FROM dbo.Fn_split (dbo.Fn_getluvalue('SFC-SEAGATE-VIRTUAL-MG', 'MG17R,MG660,MG661,MG662,MG666,MG12F,MG3C00,MG3C01,MG13F,MG529,MG530'), ',')) AND B.partno = C.partno第2阶
INSERT INTO @SourceSSNDataInfo SELECT '02' AS 'Level',--Level A.SerialSSN AS 'ParentSysSSN',--ParentSysSSN A.ActualSSN AS 'ParentActualSSN',--ParentActualSSN A.HHPN AS 'ParentHHPN',--ParentHHPN A.CustPN AS 'ParentCustPN',--ParentCustPN --B.cserialno AS 'SerialSSN', --SerialSSN CASE WHEN B.cserialno = B.partno THEN A.SerialSSN WHEN B.cserialno = (SELECT Field2 FROM mfsysprodlink WHERE Field2 = B.cserialno AND ProductID <> '' AND cserialno LIKE '11S%') THEN (SELECT ProductID FROM mfsysprodlink WHERE Field2 = B.cserialno) ELSE B.cserialno END,--if CSN=PN else GET Top Level SSN info. --dbo.fn_GetSSNByInput(B.cserialno) AS 'ActualSSN ', --ActualSSN CASE WHEN dbo.Fn_getssnbyinput(B.cserialno) = A.ActualSSN THEN B.cserialno ELSE dbo.Fn_getssnbyinput(B.cserialno) END AS 'ActualSSN ',--ActualSSN B.partno AS 'HHPN',--HHPN --B.custpartno AS 'CustPN', --CustPN C.partname AS 'CustPN',--CustPN B.categoryname AS 'CategoryName' FROM @SourceSSNDataInfo AS A, mfsyscserial AS B (NOLOCK), mmprodmaster AS C (NOLOCK) WHERE A.ActualSSN = B.sysserialno AND A.[Level] = '01' AND B.categoryname NOT IN (SELECT Value FROM dbo.Fn_split (dbo.Fn_getluvalue('SFC-SEAGATE-VIRTUAL-MG', 'MG17R,MG660,MG661,MG662,MG666,MG12F,MG3C00,MG3C01,MG13F,MG529,MG530'), ',')) AND A.ActualSSN NOT IN (SELECT ActualSSN FROM @SourceSSNDataInfo WHERE [Level] = '00') AND B.partno = C.partno第3阶
INSERT INTO @SourceSSNDataInfo SELECT '03' AS 'Level',--Level A.SerialSSN AS 'ParentSysSSN',--ParentSysSSN A.ActualSSN AS 'ParentActualSSN',--ParentActualSSN A.HHPN AS 'ParentHHPN',--ParentHHPN A.CustPN AS 'ParentCustPN',--ParentCustPN --B.cserialno AS 'SerialSSN', --SerialSSN CASE WHEN B.cserialno = B.partno THEN A.SerialSSN WHEN B.cserialno = (SELECT Field2 FROM mfsysprodlink WHERE Field2 = B.cserialno AND ProductID <> '' AND cserialno LIKE '11S%') THEN (SELECT ProductID FROM mfsysprodlink WHERE Field2 = B.cserialno) ELSE B.cserialno END,--if CSN=PN else GET Top Level SSN info. --dbo.fn_GetSSNByInput(B.cserialno) AS 'ActualSSN ', --ActualSSN CASE WHEN dbo.Fn_getssnbyinput(B.cserialno) = A.ActualSSN THEN B.cserialno ELSE dbo.Fn_getssnbyinput(B.cserialno) END AS 'ActualSSN ',--ActualSSN B.partno AS 'HHPN',--HHPN --B.custpartno AS 'CustPN', --CustPN C.partname AS 'CustPN',--CustPN B.categoryname AS 'CategoryName' FROM @SourceSSNDataInfo AS A, mfsyscserial AS B (NOLOCK), mmprodmaster AS C (NOLOCK) WHERE A.ActualSSN = B.sysserialno AND A.[Level] = '02' AND B.categoryname NOT IN (SELECT Value FROM dbo.Fn_split (dbo.Fn_getluvalue('SFC-SEAGATE-VIRTUAL-MG', 'MG17R,MG660,MG661,MG662,MG666,MG12F,MG3C00,MG3C01,MG13F,MG529,MG530'), ',')) AND A.ActualSSN NOT IN (SELECT ActualSSN FROM @SourceSSNDataInfo WHERE [Level] = '01') AND B.partno = C.partno这里多插一句，之前整体是这么写的
DECLARE @strSSN VARCHAR(50) = 'SHTJ8GKY00GETSC' --SHTJ8GKY00GETSC --FRT1011491G0C5H --XML Node Level 1 DECLARE @STR_DATA TABLE ( INPUT_MATERIAL VARCHAR(50), INPUT_SERIAL VARCHAR(50), PLANT_CODE VARCHAR(50), OPERATION VARCHAR(50), [ORDER] VARCHAR(50), PART_NUMBER VARCHAR(50), SERIAL_NUMBER VARCHAR(50), FEATURE_CODE VARCHAR(50), MATERIAL_CLASS VARCHAR(50), CHARACTERISTIC VARCHAR(50), COMPONENT_TYPE VARCHAR(50), WWN VARCHAR(50), CUST_SERIAL VARCHAR(50), ECB VARCHAR(50), EQUIPMENT_NO VARCHAR(50), MAC_ADDR VARCHAR(50), SERVICE_TAG VARCHAR(50), CUST_PART VARCHAR(50), CUST_ECREV VARCHAR(50), SYSTEM_SERIAL VARCHAR(50), [ERROR_MESSAGE] VARCHAR(50), VENDOR_PART VARCHAR(50), POSITION_REQD VARCHAR(50), DETAIL VARCHAR(50), REVISION VARCHAR(50) ) --XML Node Level 2 DECLARE @ZPE_ISTPCOMP TABLE ( ZINDEX INT IDENTITY(1, 1) NOT NULL, BM_LEVEL VARCHAR(50), PART_NUMBER VARCHAR(50), SERIAL_NUMBER VARCHAR(50), FEATURE_CODE VARCHAR(50), MATERIAL_CLASS VARCHAR(50), CHARACTERISTIC VARCHAR(50), COMPONENT_TYPE VARCHAR(50), WWN VARCHAR(50), CUST_SERIAL VARCHAR(50), PARENT_MATNR VARCHAR(50), PARENT_SERNR VARCHAR(50), ECB_C VARCHAR(50), EQUIPMENT_NO VARCHAR(50), MAC_ADDR_C VARCHAR(50), SERVICE_TAG_C VARCHAR(50), POSITION_TYPE VARCHAR(50), POSITION_NO VARCHAR(50), CUST_PART VARCHAR(50), CUST_ECREV VARCHAR(50), VENDOR_PART VARCHAR(50), POSITION_REQD VARCHAR(50), REVISION VARCHAR(50) ) DECLARE @SourceSSNDataInfo TABLE ( SeqNO INT IDENTITY(1, 1) NOT NULL, [Level] VARCHAR(50), ParentSysSSN VARCHAR(50), ParentActualSSN VARCHAR(50), ParentHHPN VARCHAR(50), ParentCustPN VARCHAR(50), SerialSSN VARCHAR(50), ActualSSN VARCHAR(50), HHPN VARCHAR(50), CustPN VARCHAR(50), CategoryName VARCHAR(50) ) DECLARE @level TABLE ( ssn VARCHAR(50), csn VARCHAR(50), LevelNum INT ) DECLARE @xmlResult XML, @xml_ForSend VARCHAR(MAX), @ERROR_MSG VARCHAR(100),--Error Message @_strActualSSN VARCHAR(50), @_strPartNO VARCHAR(50), @_strNextEvent VARCHAR(50), @_strRouteID VARCHAR(50), @_strSPError VARCHAR(100), @needChangeCustSn VARCHAR(800) SET @ERROR_MSG = '' SET @_strActualSSN = '' SET @_strPartNO = '' SET @_strNextEvent = '' SET @_strRouteID = '' SET @_strSPError = '' SET @needChangeCustSn ='' BEGIN SELECT @_strActualSSN = dbo.Fn_getssnbyinput(@strSSN) --Get PartNo by Actual SSN SELECT @_strPartNO = A.skuno, --@_strNextEvent = B.nextevent, @_strNextEvent = dbo.Fn_changesfcstation(A.skuno, B.nextevent), @_strRouteID = B.routeid FROM mfsysproduct AS A (NOLOCK), mfworkstatus AS B (NOLOCK) WHERE A.sysserialno = B.sysserialno AND A.sysserialno = @_strActualSSN DECLARE @woEcb VARCHAR(100) = '' DECLARE @virtualskuno VARCHAR(100)='' --*******************************************************Top Level SSN INSERT INTO @SourceSSNDataInfo SELECT 'TOP' AS 'Level',--Level '' AS 'ParentSysSSN',--ParentSysSSN '' AS 'ParentActualSSN',--ParentActualSSN '' AS 'ParentHHPN',--ParentHHPN '' AS 'ParentCustPN',--ParentCustPN --A.sysserialno AS 'SerialSSN', --SerialSSN --CASE WHEN LEN(LTRIM(RTRIM(B.ProductID))) <> 0 THEN B.ProductID ELSE A.sysserialno END AS 'ActualSSN ', --ActualSSN @strSSN AS 'SerialSSN',--SerialSSN A.sysserialno AS 'ActualSSN ',--ActualSSN CASE WHEN @virtualskuno = '' THEN A.skuno ELSE @virtualskuno END AS 'HHPN',--HHPN --A.custpartno AS 'CustPN', --CustPN C.partname AS 'CustPN',--CustPN A.categoryname AS 'CategoryName' FROM mfsysproduct AS A (NOLOCK), mfsysprodlink AS B (NOLOCK), mmprodmaster AS C (NOLOCK) WHERE A.sysserialno = @_strActualSSN AND A.sysserialno = B.SysSerialNo AND A.skuno = C.partno INSERT INTO @STR_DATA (INPUT_MATERIAL, INPUT_SERIAL, PLANT_CODE, OPERATION, [ORDER], PART_NUMBER, SERIAL_NUMBER, FEATURE_CODE, MATERIAL_CLASS, CHARACTERISTIC, COMPONENT_TYPE, WWN, CUST_SERIAL, ECB, EQUIPMENT_NO, MAC_ADDR, SERVICE_TAG, CUST_PART, CUST_ECREV, SYSTEM_SERIAL, [ERROR_MESSAGE], VENDOR_PART, POSITION_REQD, REVISION) SELECT '' AS 'INPUT_MATERIAL', B.SerialSSN AS 'INPUT_SERIAL', Ltrim(Rtrim(A.factoryid)) AS 'PLANT_CODE', --A.nextevent AS 'OPERATION', @_strNextEvent AS 'OPERATION', A.workorderno AS 'ORDER', B.HHPN AS 'PART_NUMBER', B.SerialSSN AS 'SERIAL_NUMBER', (SELECT FEATURE_CODE FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'FEATURE_CODE', (SELECT MATERIAL_CLASS FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'MATERIAL_CLASS', (SELECT CHARACTERISTIC FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'CHARACTERISTIC', (SELECT COMPONENT_TYPE FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'COMPONENT_TYPE', (SELECT WWN FROM dbo.Fn_getxmlnodemaininfobyssn('', B.SerialSSN, 'TOP', '')) AS 'WWN', (SELECT CUST_SERIAL FROM dbo.Fn_getxmlnodemaininfobyssn('', CASE WHEN @needChangeCustSn <> 'TRUE' THEN B.SerialSSN ELSE (SELECT Isnull(ProductID, '') FROM mfsysprodlink WHERE SysSerialNo = B.SerialSSN) END, 'TOP', '')) AS 'CUST_SERIAL', CASE WHEN @woEcb <> '' THEN @woEcb ELSE (SELECT ECB FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) END AS 'ECB', (SELECT EQUIPMENT_NO FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'EQUIPMENT_NO', (SELECT MAC_ADDR FROM dbo.Fn_getxmlnodemaininfobyssn('', B.SerialSSN, 'TOP', '')) AS 'MAC_ADDR', (SELECT SERVICE_TAG FROM dbo.Fn_getxmlnodemaininfobyssn('', B.SerialSSN, 'TOP', '')) AS 'SERVICE_TAG', --B.CustPN AS 'CUST_PART', CASE WHEN B.CustPN = B.HHPN THEN '' WHEN @needChangeCustSn = 'TRUE' THEN (SELECT M2.custpartno-- GET SUB LEVEL CUSTPARTNO FROM mfsysprodlink M1, mfsysproduct M2 WHERE M1.ProductID = M2.sysserialno AND M1.SysSerialNo = B.SerialSSN) ELSE B.CustPN END AS 'CUST_PART', '' AS 'CUST_ECREV', '' AS 'SYSTEM_SERIAL', @ERROR_MSG AS 'ERROR_MESSAGE', --LTRIM(RTRIM(C.manufacturerpn)) AS 'VENDOR_PART', CASE WHEN Ltrim(Rtrim(C.manufacturerpn)) = B.HHPN THEN '' ELSE Ltrim(Rtrim(C.manufacturerpn)) END AS 'VENDOR_PART', '' AS 'POSITION_REQD', '' AS 'REVISION' FROM mfworkstatus AS A (NOLOCK) RIGHT JOIN @SourceSSNDataInfo AS B ON A.sysserialno = B.ActualSSN INNER JOIN mmprodmaster AS C (NOLOCK) ON B.HHPN = C.partno IF @@ERROR <> 0 BEGIN SET @_strSPError = 'Insert Top Level Data Error.' RAISERROR (@_strSPError,16,1) END --*******************************************************Level 00 SSN INSERT INTO @SourceSSNDataInfo SELECT '00' AS 'Level',--Level '' AS 'ParentSysSSN',--ParentSysSSN '' AS 'ParentActualSSN',--ParentActualSSN '' AS 'ParentHHPN',--ParentHHPN '' AS 'ParentCustPN',--ParentCustPN --A.sysserialno AS 'SerialSSN', --SerialSSN --CASE WHEN LEN(LTRIM(RTRIM(B.ProductID))) <> 0 THEN B.ProductID ELSE A.sysserialno END AS 'ActualSSN ', --ActualSSN @strSSN AS 'SerialSSN',--SerialSSN A.sysserialno AS 'ActualSSN ',--ActualSSN CASE WHEN @virtualskuno = '' THEN A.skuno ELSE @virtualskuno END AS 'HHPN',--HHPN --A.custpartno AS 'CustPN', --CustPN C.partname AS 'CustPN',--CustPN A.categoryname AS 'CategoryName' FROM mfsysproduct AS A (NOLOCK), mfsysprodlink AS B (NOLOCK), mmprodmaster AS C (NOLOCK) WHERE A.sysserialno = @_strActualSSN AND A.sysserialno = B.SysSerialNo AND A.skuno = C.partno --Insert Level 00 Info IF EXISTS (SELECT 0 FROM @SourceSSNDataInfo WHERE [Level] = '00') BEGIN INSERT INTO @ZPE_ISTPCOMP SELECT '00' AS 'BM_LEVEL', B.HHPN AS 'PART_NUMBER', B.SerialSSN AS 'SERIAL_NUMBER', (SELECT FEATURE_CODE FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'FEATURE_CODE', (SELECT MATERIAL_CLASS FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'MATERIAL_CLASS', (SELECT CHARACTERISTIC FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'CHARACTERISTIC', (SELECT COMPONENT_TYPE FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'COMPONENT_TYPE', (SELECT WWN FROM dbo.Fn_getxmlnodemaininfobyssn('', B.SerialSSN, '00', '')) AS 'WWN', (SELECT CUST_SERIAL FROM dbo.Fn_getxmlnodemaininfobyssn('', CASE WHEN @needChangeCustSn <> 'TRUE' THEN B.SerialSSN ELSE (SELECT Isnull(ProductID, '') FROM mfsysprodlink WHERE SysSerialNo = B.SerialSSN) END, 'TOP', '')) AS 'CUST_SERIAL', '' AS 'PARENT_MATNR', '' AS 'PARENT_SERNR', (SELECT ECB FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'ECB_C', (SELECT EQUIPMENT_NO FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'EQUIPMENT_NO', (SELECT MAC_ADDR FROM dbo.Fn_getxmlnodemaininfobyssn('', B.SerialSSN, '00', '')) AS 'MAC_ADDR_C', (SELECT SERVICE_TAG FROM dbo.Fn_getxmlnodemaininfobyssn('', B.SerialSSN, '00', '')) AS 'SERVICE_TAG_C', '' AS 'POSITION_TYPE', '' AS 'POSITION_NO', --B.CustPN AS 'CUST_PART', CASE WHEN B.CustPN = B.HHPN THEN '' WHEN @needChangeCustSn = 'TRUE' THEN (SELECT M2.custpartno-- GET SUB LEVEL CUSTPARTNO FROM mfsysprodlink M1, mfsysproduct M2 WHERE M1.ProductID = M2.sysserialno AND M1.SysSerialNo = B.SerialSSN) ELSE B.CustPN END AS 'CUST_PART', '' AS 'CUST_ECREV', --LTRIM(RTRIM(C.manufacturerpn)) AS 'VENDOR_PART', CASE WHEN Ltrim(Rtrim(C.manufacturerpn)) = B.HHPN THEN '' ELSE Ltrim(Rtrim(C.manufacturerpn)) END AS 'VENDOR_PART', '' AS 'POSITION_REQD', '' AS 'REVISION' FROM mfworkstatus AS A (NOLOCK) RIGHT JOIN @SourceSSNDataInfo AS B ON A.sysserialno = B.ActualSSN AND B.[Level] = '00' INNER JOIN mmprodmaster AS C (NOLOCK) ON B.[Level] = '00' AND B.HHPN = C.partno IF @@ERROR <> 0 BEGIN SET @_strSPError = 'Insert Level 00 Data Error.' RAISERROR (@_strSPError,16,1) END END --*******************************************************Level 01 SSN IF EXISTS (SELECT 0 FROM @SourceSSNDataInfo WHERE [Level] = '00') BEGIN INSERT INTO @SourceSSNDataInfo SELECT '01' AS 'Level',--Level A.SerialSSN AS 'ParentSysSSN',--ParentSysSSN A.ActualSSN AS 'ParentActualSSN',--ParentActualSSN A.HHPN AS 'ParentHHPN',--ParentHHPN A.CustPN AS 'ParentCustPN',--ParentCustPN --B.cserialno AS 'SerialSSN', --SerialSSN CASE WHEN B.cserialno = B.partno THEN A.SerialSSN WHEN B.cserialno = (SELECT Field2 FROM mfsysprodlink WHERE Field2 = B.cserialno AND ProductID <> '' AND cserialno LIKE '11S%') THEN (SELECT ProductID FROM mfsysprodlink WHERE Field2 = B.cserialno) ELSE B.cserialno END,--if CSN=PN else GET Top Level SSN info. --dbo.fn_GetSSNByInput(B.cserialno) AS 'ActualSSN ', --ActualSSN CASE WHEN dbo.Fn_getssnbyinput(B.cserialno) = A.ActualSSN THEN B.cserialno ELSE dbo.Fn_getssnbyinput(B.cserialno) END AS 'ActualSSN ',--ActualSSN Replace (B.partno, '-HH', '') AS 'HHPN',--HHPN --B.custpartno AS 'CustPN', --CustPN C.partname AS 'CustPN',--CustPN B.categoryname AS 'CategoryName' FROM @SourceSSNDataInfo AS A, mfsyscserial AS B (NOLOCK), mmprodmaster AS C (NOLOCK) WHERE A.ActualSSN = B.sysserialno AND A.[Level] = '00' AND B.categoryname NOT IN (SELECT Value FROM dbo.Fn_split (dbo.Fn_getluvalue('SFC-SEAGATE-VIRTUAL-MG', 'MG17R,MG660,MG661,MG662,MG666,MG12F,MG3C00,MG3C01,MG13F,MG529,MG530'), ',')) AND B.partno = C.partno IF EXISTS (SELECT 0 FROM @SourceSSNDataInfo WHERE [Level] = '01') BEGIN INSERT INTO @ZPE_ISTPCOMP SELECT '01' AS 'BM_LEVEL', B.HHPN AS 'PART_NUMBER', --B.SerialSSN AS 'SERIAL_NUMBER', --For DDIC&DELL Special Rule CASE WHEN Len(Ltrim(Rtrim(B.SerialSSN))) > 15 AND B.CategoryName IN (SELECT Value FROM dbo.Fn_split (dbo.Fn_getluvalue('SFC-SEAGATE-DDIC-SPECIAL-MG', 'MG12L,MG01'), ',')) THEN LEFT(B.SerialSSN, 8) + Substring(B.SerialSSN, 14, 20) ELSE B.SerialSSN END AS 'SERIAL_NUMBER', (SELECT FEATURE_CODE FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'FEATURE_CODE', (SELECT MATERIAL_CLASS FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'MATERIAL_CLASS', (SELECT CHARACTERISTIC FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'CHARACTERISTIC', (SELECT COMPONENT_TYPE FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'COMPONENT_TYPE', (SELECT WWN FROM dbo.Fn_getxmlnodemaininfobyssn(B.ParentActualSSN, B.SerialSSN, '01', B.CategoryName)) AS 'WWN', ( CASE WHEN ( EXISTS (SELECT 1 FROM mfsyscserial E, mfsysprodlink F WHERE E.cserialno = F.Field2 AND F.ProductID = B.SerialSSN) ) THEN (SELECT field2 FROM mfsysprodlink WHERE ProductID = B.SerialSSN) ELSE (SELECT CUST_SERIAL FROM dbo.Fn_getxmlnodemaininfobyssn(B.ParentActualSSN, B.SerialSSN, '01', B.CategoryName)) END ) AS 'CUST_SERIAL', B.ParentHHPN AS 'PARENT_MATNR', B.ParentSysSSN AS 'PARENT_SERNR', (SELECT ECB FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'ECB_C', (SELECT EQUIPMENT_NO FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'EQUIPMENT_NO', (SELECT MAC_ADDR FROM dbo.Fn_getxmlnodemaininfobyssn(B.ParentActualSSN, B.SerialSSN, '01', B.CategoryName)) AS 'MAC_ADDR_C', (SELECT SERVICE_TAG FROM dbo.Fn_getxmlnodemaininfobyssn(B.ParentActualSSN, B.SerialSSN, '01', B.CategoryName)) AS 'SERVICE_TAG_C', '' AS 'POSITION_TYPE', '' AS 'POSITION_NO', --B.CustPN AS 'CUST_PART', CASE WHEN B.CustPN = B.HHPN THEN '' ELSE B.CustPN END AS 'CUST_PART', '' AS 'CUST_ECREV', --LTRIM(RTRIM(C.manufacturerpn)) AS 'VENDOR_PART', CASE WHEN Ltrim(Rtrim(C.manufacturerpn)) = B.HHPN THEN '' ELSE Ltrim(Rtrim(C.manufacturerpn)) END AS 'VENDOR_PART', '' AS 'POSITION_REQD', '' AS 'REVISION' FROM mfworkstatus AS A (NOLOCK) RIGHT JOIN @SourceSSNDataInfo AS B ON A.sysserialno = B.ActualSSN AND B.[Level] = '01' INNER JOIN mmprodmaster AS C (NOLOCK) ON B.[Level] = '01' AND B.HHPN = C.partno IF @@ERROR <> 0 BEGIN SET @_strSPError = 'Insert Level 01 Data Error.' RAISERROR (@_strSPError,16,1) END END END --*******************************************************Level 02 SSN IF EXISTS (SELECT 0 FROM @SourceSSNDataInfo WHERE [Level] = '01') BEGIN --PRINT '02' INSERT INTO @SourceSSNDataInfo SELECT '02' AS 'Level',--Level A.SerialSSN AS 'ParentSysSSN',--ParentSysSSN A.ActualSSN AS 'ParentActualSSN',--ParentActualSSN A.HHPN AS 'ParentHHPN',--ParentHHPN A.CustPN AS 'ParentCustPN',--ParentCustPN --B.cserialno AS 'SerialSSN', --SerialSSN CASE WHEN B.cserialno = B.partno THEN A.SerialSSN WHEN B.cserialno = (SELECT Field2 FROM mfsysprodlink WHERE Field2 = B.cserialno AND ProductID <> '' AND cserialno LIKE '11S%') THEN (SELECT ProductID FROM mfsysprodlink WHERE Field2 = B.cserialno) ELSE B.cserialno END,--if CSN=PN else GET Top Level SSN info. --dbo.fn_GetSSNByInput(B.cserialno) AS 'ActualSSN ', --ActualSSN CASE WHEN dbo.Fn_getssnbyinput(B.cserialno) = A.ActualSSN THEN B.cserialno ELSE dbo.Fn_getssnbyinput(B.cserialno) END AS 'ActualSSN ',--ActualSSN B.partno AS 'HHPN',--HHPN --B.custpartno AS 'CustPN', --CustPN C.partname AS 'CustPN',--CustPN B.categoryname AS 'CategoryName' FROM @SourceSSNDataInfo AS A, mfsyscserial AS B (NOLOCK), mmprodmaster AS C (NOLOCK) WHERE A.ActualSSN = B.sysserialno AND A.[Level] = '01' AND B.categoryname NOT IN (SELECT Value FROM dbo.Fn_split (dbo.Fn_getluvalue('SFC-SEAGATE-VIRTUAL-MG', 'MG17R,MG660,MG661,MG662,MG666,MG12F,MG3C00,MG3C01,MG13F,MG529,MG530'), ',')) AND A.ActualSSN NOT IN (SELECT ActualSSN FROM @SourceSSNDataInfo WHERE [Level] = '00') AND B.partno = C.partno --Insert Level 02 info IF EXISTS (SELECT 0 FROM @SourceSSNDataInfo WHERE [Level] = '02') BEGIN INSERT INTO @ZPE_ISTPCOMP SELECT '02' AS 'BM_LEVEL', B.HHPN AS 'PART_NUMBER', --B.SerialSSN AS 'SERIAL_NUMBER', --For DDIC&DELL Special Rule CASE WHEN Len(Ltrim(Rtrim(B.SerialSSN))) > 15 AND B.CategoryName IN (SELECT Value FROM dbo.Fn_split (dbo.Fn_getluvalue('SFC-SEAGATE-DDIC-SPECIAL-MG', 'MG12L,MG01'), ',')) THEN LEFT(B.SerialSSN, 8) + Substring(B.SerialSSN, 14, 20) ELSE B.SerialSSN END AS 'SERIAL_NUMBER', (SELECT FEATURE_CODE FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'FEATURE_CODE', (SELECT MATERIAL_CLASS FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'MATERIAL_CLASS', (SELECT CHARACTERISTIC FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'CHARACTERISTIC', (SELECT COMPONENT_TYPE FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'COMPONENT_TYPE', (SELECT WWN FROM dbo.Fn_getxmlnodemaininfobyssn(B.ParentActualSSN, B.SerialSSN, '02', B.CategoryName)) AS 'WWN', ( CASE WHEN ( EXISTS (SELECT 1 FROM mfsyscserial E, mfsysprodlink F WHERE E.cserialno = F.Field2 AND F.ProductID = B.SerialSSN) ) THEN (SELECT field2 FROM mfsysprodlink WHERE ProductID = B.SerialSSN) ELSE (SELECT CUST_SERIAL FROM dbo.Fn_getxmlnodemaininfobyssn(B.ParentActualSSN, B.SerialSSN, '02', B.CategoryName)) END ) AS 'CUST_SERIAL', B.ParentHHPN AS 'PARENT_MATNR', B.ParentSysSSN AS 'PARENT_SERNR', (SELECT ECB FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'ECB_C', (SELECT EQUIPMENT_NO FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'EQUIPMENT_NO', (SELECT MAC_ADDR FROM dbo.Fn_getxmlnodemaininfobyssn(B.ParentActualSSN, B.SerialSSN, '02', B.CategoryName)) AS 'MAC_ADDR_C', (SELECT SERVICE_TAG FROM dbo.Fn_getxmlnodemaininfobyssn(B.ParentActualSSN, B.SerialSSN, '02', B.CategoryName)) AS 'SERVICE_TAG_C', '' AS 'POSITION_TYPE', '' AS 'POSITION_NO', --B.CustPN AS 'CUST_PART', CASE WHEN B.CustPN = B.HHPN THEN '' ELSE B.CustPN END AS 'CUST_PART', '' AS 'CUST_ECREV', --LTRIM(RTRIM(C.manufacturerpn)) AS 'VENDOR_PART', CASE WHEN Ltrim(Rtrim(C.manufacturerpn)) = B.HHPN THEN '' ELSE Ltrim(Rtrim(C.manufacturerpn)) END AS 'VENDOR_PART', '' AS 'POSITION_REQD', '' AS 'REVISION' FROM mfworkstatus AS A (NOLOCK) RIGHT JOIN @SourceSSNDataInfo AS B ON A.sysserialno = B.ActualSSN AND B.[Level] = '02' INNER JOIN mmprodmaster AS C (NOLOCK) ON B.[Level] = '02' AND B.HHPN = C.partno IF @@ERROR <> 0 BEGIN SET @_strSPError = 'Insert Level 02 Data Error.' RAISERROR (@_strSPError,16,1) END END END --*******************************************************Level 03 SSN IF EXISTS (SELECT 0 FROM @SourceSSNDataInfo WHERE [Level] = '02') BEGIN --PRINT '03' INSERT INTO @SourceSSNDataInfo SELECT '03' AS 'Level',--Level A.SerialSSN AS 'ParentSysSSN',--ParentSysSSN A.ActualSSN AS 'ParentActualSSN',--ParentActualSSN A.HHPN AS 'ParentHHPN',--ParentHHPN A.CustPN AS 'ParentCustPN',--ParentCustPN --B.cserialno AS 'SerialSSN', --SerialSSN CASE WHEN B.cserialno = B.partno THEN A.SerialSSN WHEN B.cserialno = (SELECT Field2 FROM mfsysprodlink WHERE Field2 = B.cserialno AND ProductID <> '' AND cserialno LIKE '11S%') THEN (SELECT ProductID FROM mfsysprodlink WHERE Field2 = B.cserialno) ELSE B.cserialno END,--if CSN=PN else GET Top Level SSN info. --dbo.fn_GetSSNByInput(B.cserialno) AS 'ActualSSN ', --ActualSSN CASE WHEN dbo.Fn_getssnbyinput(B.cserialno) = A.ActualSSN THEN B.cserialno ELSE dbo.Fn_getssnbyinput(B.cserialno) END AS 'ActualSSN ',--ActualSSN B.partno AS 'HHPN',--HHPN --B.custpartno AS 'CustPN', --CustPN C.partname AS 'CustPN',--CustPN B.categoryname AS 'CategoryName' FROM @SourceSSNDataInfo AS A, mfsyscserial AS B (NOLOCK), mmprodmaster AS C (NOLOCK) WHERE A.ActualSSN = B.sysserialno AND A.[Level] = '02' AND B.categoryname NOT IN (SELECT Value FROM dbo.Fn_split (dbo.Fn_getluvalue('SFC-SEAGATE-VIRTUAL-MG', 'MG17R,MG660,MG661,MG662,MG666,MG12F,MG3C00,MG3C01,MG13F,MG529,MG530'), ',')) AND A.ActualSSN NOT IN (SELECT ActualSSN FROM @SourceSSNDataInfo WHERE [Level] = '01') AND B.partno = C.partno --Insert Level 03 info select * from @SourceSSNDataInfo IF EXISTS (SELECT 0 FROM @SourceSSNDataInfo WHERE [Level] = '03') BEGIN INSERT INTO @ZPE_ISTPCOMP SELECT '03' AS 'BM_LEVEL', B.HHPN AS 'PART_NUMBER', --B.SerialSSN AS 'SERIAL_NUMBER', --For DDIC&DELL Special Rule CASE WHEN Len(Ltrim(Rtrim(B.SerialSSN))) > 15 AND B.CategoryName IN (SELECT Value FROM dbo.Fn_split (dbo.Fn_getluvalue('SFC-SEAGATE-DDIC-SPECIAL-MG', 'MG12L,MG01'), ',')) THEN LEFT(B.SerialSSN, 8) + Substring(B.SerialSSN, 14, 20) ELSE B.SerialSSN END AS 'SERIAL_NUMBER', (SELECT FEATURE_CODE FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'FEATURE_CODE', (SELECT MATERIAL_CLASS FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'MATERIAL_CLASS', (SELECT CHARACTERISTIC FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'CHARACTERISTIC', (SELECT COMPONENT_TYPE FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'COMPONENT_TYPE', (SELECT WWN FROM dbo.Fn_getxmlnodemaininfobyssn(B.ParentActualSSN, B.SerialSSN, '03', B.CategoryName)) AS 'WWN', ( CASE WHEN ( EXISTS (SELECT 1 FROM mfsyscserial E, mfsysprodlink F WHERE E.cserialno = F.Field2 AND F.ProductID = B.SerialSSN) ) THEN (SELECT field2 FROM mfsysprodlink WHERE ProductID = B.SerialSSN) ELSE (SELECT CUST_SERIAL FROM dbo.Fn_getxmlnodemaininfobyssn(B.ParentActualSSN, B.SerialSSN, '03', B.CategoryName)) END ) AS 'CUST_SERIAL', B.ParentHHPN AS 'PARENT_MATNR', B.ParentSysSSN AS 'PARENT_SERNR', (SELECT ECB FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'ECB_C', (SELECT EQUIPMENT_NO FROM dbo.Fn_getxmlnodeotherinfobypn(B.HHPN)) AS 'EQUIPMENT_NO', (SELECT MAC_ADDR FROM dbo.Fn_getxmlnodemaininfobyssn(B.ParentActualSSN, B.SerialSSN, '03', B.CategoryName)) AS 'MAC_ADDR_C', (SELECT SERVICE_TAG FROM dbo.Fn_getxmlnodemaininfobyssn(B.ParentActualSSN, B.SerialSSN, '03', B.CategoryName)) AS 'SERVICE_TAG_C', '' AS 'POSITION_TYPE', '' AS 'POSITION_NO', --B.CustPN AS 'CUST_PART', CASE WHEN B.CustPN = B.HHPN THEN '' ELSE B.CustPN END AS 'CUST_PART', '' AS 'CUST_ECREV', --LTRIM(RTRIM(C.manufacturerpn)) AS 'VENDOR_PART', CASE WHEN Ltrim(Rtrim(C.manufacturerpn)) = B.HHPN THEN '' ELSE Ltrim(Rtrim(C.manufacturerpn)) END AS 'VENDOR_PART', '' AS 'POSITION_REQD', '' AS 'REVISION' FROM mfworkstatus AS A (NOLOCK) RIGHT JOIN @SourceSSNDataInfo AS B ON A.sysserialno = B.ActualSSN AND B.[Level] = '03' INNER JOIN mmprodmaster AS C (NOLOCK) ON B.[Level] = '03' AND B.HHPN = C.partno IF @@ERROR <> 0 BEGIN SET @_strSPError = 'Insert Level 03 Data Error.' RAISERROR (@_strSPError,16,1) END END END SELECT @xmlResult = (SELECT INPUT_MATERIAL, INPUT_SERIAL, PLANT_CODE, CASE WHEN OPERATION IS NULL THEN '' ELSE OPERATION END AS OPERATION, [ORDER], PART_NUMBER, SERIAL_NUMBER, CASE WHEN FEATURE_CODE IS NULL THEN '' ELSE FEATURE_CODE END AS FEATURE_CODE, MATERIAL_CLASS, CHARACTERISTIC, COMPONENT_TYPE, CASE WHEN WWN IS NULL THEN '' ELSE WWN END AS WWN, CUST_SERIAL, CASE WHEN ECB IS NULL THEN '' ELSE ECB END AS ECB, EQUIPMENT_NO, MAC_ADDR, SERVICE_TAG, CUST_PART, CUST_ECREV, SYSTEM_SERIAL, [ERROR_MESSAGE], VENDOR_PART, POSITION_REQD, REVISION, (SELECT (SELECT CASE Len(ZINDEX) WHEN 1 THEN '000' + CONVERT(VARCHAR, ZINDEX) WHEN 2 THEN '00' + CONVERT(VARCHAR, ZINDEX) WHEN 3 THEN '0' + CONVERT(VARCHAR, ZINDEX) WHEN 4 THEN CONVERT(VARCHAR, ZINDEX) END) AS ZINDEX, BM_LEVEL, PART_NUMBER, SERIAL_NUMBER, FEATURE_CODE, MATERIAL_CLASS, CHARACTERISTIC, COMPONENT_TYPE, CASE WHEN WWN IS NULL THEN '' ELSE WWN END AS WWN, CUST_SERIAL, PARENT_MATNR, PARENT_SERNR, CASE WHEN ECB IS NULL THEN '' ELSE ECB END AS ECB_C, EQUIPMENT_NO, MAC_ADDR_C, SERVICE_TAG_C, POSITION_TYPE, POSITION_NO, CUST_PART, CUST_ECREV, VENDOR_PART, POSITION_REQD, REVISION FROM @ZPE_ISTPCOMP FOR XML PATH('ZPE_ISTPCOMP'), TYPE, ROOT('DETAIL')) FROM @STR_DATA FOR XML PATH(''), TYPE) SET @xml_ForSend = CONVERT(VARCHAR(MAX), @xmlResult) SELECT '<?xml version="1.0" encoding="UTF-8"?><STR_DATA xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="zpe_istpcomp.xsd">' + @xml_ForSend + '</STR_DATA>' END最终改写为
DELETE @SourceSSNDataInfo; --start recursion(递归),and insert into @SourceSSNDataInfo WITH cte(level, ParentSysSSN, ParentActualSSN, ParentHHPN, ParentCustPN,SerialSSN,ActualSSN,HHPN,CustPN,CategoryName,tempHHPN) AS (SELECT 0 AS level, cast('' as varchar(100)), cast(''as varchar(100)), cast( ''as varchar(100)), cast( ''as varchar(100)), cast(@strSSN as varchar(100)), cast(@_strActualSSN as varchar(100)), cast((CASE WHEN @virtualskuno = '' THEN @_strPartNO ELSE @virtualskuno END) as varchar(100)), cast(c.partname as varchar(100)), cast(a.categoryname as varchar(100)), cast('' as varchar(100)) FROM mfsysproduct a inner join mmprodmaster c on a.skuno = c.partno WHERE a.sysserialno = @_strActualSSN UNION ALL SELECT level + 1, cast(cte.SerialSSN as varchar(100)), cast(cte.ActualSSN as varchar(100)), cast(cte.HHPN as varchar(100)), cast(cte.CustPN as varchar(100)), cast((CASE WHEN B.cserialno = B.partno THEN cte.SerialSSN WHEN B.cserialno = (SELECT Field2 FROM mfsysprodlink WHERE Field2 = B.cserialno AND ProductID <> '' AND cserialno LIKE '11S%') THEN (SELECT ProductID FROM mfsysprodlink WHERE Field2 = B.cserialno) ELSE B.cserialno END) as varchar(100)), cast( (CASE WHEN dbo.Fn_getssnbyinput(B.cserialno) = cte.ActualSSN THEN B.cserialno ELSE dbo.Fn_getssnbyinput(B.cserialno) END) as varchar(100)), cast( Replace (B.partno, '-HH', '') as varchar(100)), cast( C.partname as varchar(100)), cast( B.categoryname as varchar(100)), cast(b.partno as varchar(100)) FROM cte INNER JOIN mfsyscserial b inner join mmprodmaster c on c.partno = b.partno ON b.sysserialno = cte.ActualSSN AND NOT EXISTS (SELECT Value FROM dbo.Fn_split (dbo.Fn_getluvalue('SFC-SEAGATE-VIRTUAL-MG', 'MG17R,MG660,MG661,MG662,MG666,MG12F,MG3C00,MG3C01,MG13F,MG529,MG530'), ',') WHERE b.categoryname = Value)) INSERT INTO @SourceSSNDataInfo SELECT Replicate('0', 2-Len(CONVERT(VARCHAR(2), cte.level))) + CONVERT(VARCHAR(2), cte.level) AS 'LevelNum', cte.ParentSysSSN AS 'ParentSysSSN',--SerialSSN cte.ParentActualSSN AS 'ParentActualSSN', cte.ParentHHPN AS 'ParentHHPN', cte.ParentCustPN AS 'ParentCustPN',--CustPN cte.SerialSSN AS 'SerialSSN',--SerialSSN , cte.ActualSSN AS 'ActualSSN ',--ActualSSN (CASE WHEN cte.level = 1 THEN cte.HHPN ELSE cte.tempHHPN END) AS 'HHPN',--HHPN cte.CustPN AS 'CustPN',--CustPN cte.categoryname AS 'CategoryName' FROM cte order by cte.level select * from @SourceSSNDataInfo