1. SQLServer事务、游标、遍历、GOTO、临时表、异常等
--SQLServer中Job不支持事务，需要去掉事务相关语句即可，只保留rollback。异常同样会回滚，不影响
ALTER PROCEDURE Proc_auto3debomtok3
AS
BEGIN
IF Object_id('TEMPDB..#t_3de_dbom_1') IS NOT NULL
DROP TABLE #t_3de_dbom_1;
CREATE TABLE #t_3de_dbom_1
(
id INT PRIMARY KEY,
parent_code VARCHAR(100),
sub_code VARCHAR(100),
number DECIMAL(18, 2),
remark VARCHAR(1000)
)
DECLARE @id INT,
@level INT,
@number INT,
@finterid INT=0,
@FBootID INT=0,
@fuserid INT=0,
@xh INT=0,
@state INT
DECLARE @parent_code VARCHAR(50),
@sub_code VARCHAR(50),
@version VARCHAR(50),
@remark VARCHAR(500),
@return VARCHAR(500)=''
DECLARE @createtime DATETIME
SELECT @fuserid = fuserid
FROM t_user
WHERE fname = '3DE'
DECLARE order_cursor CURSOR FOR
(SELECT t.[id],
t.[structure_level],
t.[parent_code],
t.[sub_code],
t.[version_number],
t.[number],
t.[remark],
t.create_time
FROM t_3de_dbom t
WHERE t.structure_level = 0
--AND t.id NOT IN ( 62 )
)
--打开游标--
OPEN order_cursor
--开始循环游标变量--
FETCH next FROM order_cursor INTO @id,
@level,
@parent_code,
@sub_code,
@version,
@number,
@remark,
@createtime
WHILE @@FETCH_STATUS = 0 --返回被 FETCH语句执行的最后游标的状态--
BEGIN
SET @return=''
SET @state=0
INSERT INTO #t_3de_dbom_1
SELECT bom.id,
bom.parent_code,
bom.sub_code,
bom.number,
bom.remark
FROM t_3de_dbom bom
WHERE bom.id = @id
OR ( bom.parent_code = @sub_code
AND Isnull(bom.create_time, '1900/01/01') = Isnull(@createtime, '1900/01/01') )
IF @createtime IS NULL
BEGIN
SET @return='BOM创建时间【create_time】不能为空'
GOTO THEEND
END
BEGIN
BEGIN TRAN
BEGIN TRY
SELECT TOP 1 @return = 'BOM同组物料【' + bom.sub_code + '】不存在'
FROM #t_3de_dbom_1 bom
LEFT JOIN t_ICItem t
ON t.FNumber = bom.sub_code
WHERE t.FItemID IS NULL
IF @return != ''
BEGIN
COMMIT TRAN
GOTO THEEND
END
--处理其本身的数据【父级】
SELECT @FBootID = Isnull(Max(x.FBootID), 0)
FROM icbomgroup x
WHERE x.fnumber = @sub_code;
IF @FBootID = 0
BEGIN
INSERT INTO icbomgroup
(finterid,
fparentid,
fname,
fnumber,
fbootid)
SELECT Isnull((SELECT Max(ac.finterid) FROM icbomgroup ac), 0) + 1 AS finterid,
0 AS fparentid,
x.fname,
x.fnumber,
Isnull((SELECT Max(ac.finterid) FROM icbomgroup ac), 0) + 1 AS fbootid
FROM t_ICItem x
WHERE x.fnumber = @sub_code --AND x.fdeleted = 0
END
SELECT @finterid = Isnull(Max(x.finterid), 0)
FROM icbom x
INNER JOIN t_icitem t
ON x.FItemID = t.FItemID
WHERE t.fnumber = @sub_code
AND x.FUseStatus = 1072
IF @finterid = 0
BEGIN
INSERT INTO icbom
(fbrno,
finterid,
fbomnumber,
FImpMode,
FUseStatus,
FVersion,
FParentID,
FItemID,
FQty,
FYield,
FCheckID,
FCheckDate,
FOperatorID,
FEnterTime,
FStatus,
FCancellation,
FTranType,
FRoutingID,
FBomType,
FCustID,
FCustItemID,
FAccessories,
FNote,
FUnitID,
FAUXQTY,
FCheckerID,
FAudDate,
FEcnInterID,
FBeenChecked,
FForbid,
FAuxPropID,
FPDMImportDate,
FBOMSkip,
FClassTypeID,
FUserID,
FUseDate)
SELECT 0 AS fbrno,
Isnull((SELECT Max(ac.finterid) FROM icbom ac), 0) + 1 AS finterid,
y.fnumber AS fbomnumber,
0 AS fimpmode,
1072 AS FUseStatus,
y.fversion,
z.FBootID AS fpqrentid,
y.fitemid,
x.number,
100 AS FYield,
@fuserid AS FCheckID,
CONVERT(DATE, Getdate()) AS FCheckDate,
@fuserid AS FOperatorID,
CONVERT(DATE, Getdate()) AS FEnterTime,
1 AS FStatus,
0 AS FCancellation,
50 AS FTranType,
0 AS FRoutingID,
0 AS FBomType,
0 AS FCustID,
0 AS FCustItemID,
0 AS FAccessories,
'3DE-DBOM' AS FNote,
y.FUnitID,
x.number AS FAUXQTY,
16394 AS FCheckerID,
CONVERT(DATE, Getdate()) AS FAudDate,
0 AS FEcnInterID,
1 AS FBeenChecked,
0 AS FForbid,
0 AS FAuxPropID,
NULL AS FPDMImportDate,
1059 AS FBOMSkip,
1002522 AS FClassTypeID,
@fuserid AS FUserID,
CONVERT(DATE, Getdate()) AS FUseDate
FROM #t_3de_dbom_1 x
INNER JOIN t_icitem y
ON y.fnumber = x.sub_code
INNER JOIN icbomgroup z
ON z.fnumber = x.sub_code
WHERE x.ID = @id;
END
ELSE
BEGIN
UPDATE icbom
SET FVersion = @version,
FOperatorID = @fuserid,
FEnterTime = CONVERT(DATE, Getdate()),
FParentID = @FBootID
WHERE finterid = @finterid
DELETE FROM icbomchild
WHERE finterid = @finterid
END
SELECT @xh = Min(id) - 1
FROM #t_3de_dbom_1
WHERE parent_code = @sub_code
--新增icbomchild子项数据
INSERT INTO icbomchild
(FBrNo,
FEntryID,
FInterID,
FItemID,
FAuxQty,
FQty,
FScrap,
FOperSN,
FOperID,
FMachinePos,
FNote,
FMaterielType,
FMarshalType,
FPercent,
FBeginDay,
FEndDay,
FOffSetDay,
FBackFlush,
FStockID,
FSPID,
FSupply,
FUnitID,
FAuxPropID,
FPDMImportDate,
FPositionNo,
FItemSize,
FItemSuite,
FNote1,
FNote2,
FNote3,
FHasChar,
FDetailID,
FCostPercentage,
FEntrySelfZ0149)
SELECT 0 AS FBrNo,
b1.id - @xh AS FEntryID,
b4.FInterID AS FInterID,
b2.FItemID AS FItemID,
b1.number AS FAuxQty,
b1.number AS FQty,
0 AS FScrap,
0 AS FOperSN,
0 AS FOperID,
'' AS FMachinePos,
'' AS FNote,
371 AS FMaterielType,
385 AS FMarshalType,
100 AS FPercent,
'1900-01-01' AS FBeginDay,
'2100-01-01' AS FEndDay,
0 AS FOffSetDay,
1059 AS FBackFlush,
0 AS FStockID,
0 AS FSPID,
0 AS FSupply,
b2.FUnitID AS FUnitID,
0 AS FAuxPropID,
NULL AS FPDMImportDate,
'' AS FPositionNo,
'' AS FItemSize,
'' AS FItemSuite,
'3DE-DBOM' AS FNote1,
'' AS FNote2,
'' AS FNote3,
0 AS FHasChar,
Newid() AS FDetailID,
NULL AS FCostPercentage,
b1.remark
FROM #t_3de_dbom_1 b1
INNER JOIN t_ICItem b2 --子项
ON b2.fnumber = b1.sub_code
AND b2.FDeleted = 0
INNER JOIN t_ICItem b3 --父项
ON b3.fnumber = b1.parent_code
INNER JOIN ICBOM b4
ON b3.FItemID = b4.FItemID
WHERE b1.parent_code = @sub_code
AND NOT EXISTS(SELECT 1
FROM icbomchild i
WHERE i.finterid = b4.finterid
AND i.FItemID = b2.FItemID
AND i.fqty = b1.number)
SET @state=1
--IF @id = 62
-- SET @xh='2023/01/01'
COMMIT TRAN
GOTO THEEND
END TRY
BEGIN CATCH
ROLLBACK TRAN
SET @state=0
SELECT @return = '数据出现异常，回滚事务。具体原因：' + Error_message()
--RAISERROR(@return,18,18)
GOTO THEEND
END CATCH
END
THEEND:
BEGIN
UPDATE lg
SET lg.state = @state,
lg.remark = @return
FROM t_3de_bom_log lg
INNER JOIN #t_3de_dbom_1 bom
ON lg.id = bom.id
INSERT INTO [dbo].[t_3de_bom_log]
([id],
[state],
[remark])
SELECT id,
@state,
@return
FROM #t_3de_dbom_1 bom
WHERE NOT EXISTS(SELECT 1
FROM t_3de_bom_log t
WHERE bom.id = t.id)
IF @state = 1
BEGIN
UPDATE his
SET his.[structure_level] = bom.[structure_level],
his.[parent_code] = bom.[parent_code],
his.[sub_code] = bom.[sub_code],
his.[sub_name] = bom.[sub_name],
his.[specification] = bom.[specification],
his.[number] = bom.[number],
his.[version_number] = bom.[version_number],
his.[state_index] = bom.[state_index],
his.[state] = bom.[state],
his.[linenum] = bom.[linenum],
his.[accession_number] = bom.[accession_number],
his.[bit_number] = bom.[bit_number],
his.[remark] = bom.[remark],
his.[create_time] = bom.[create_time]
FROM [dbo].[t_3de_dbom_his] his
INNER JOIN #t_3de_dbom_1 bom1
ON his.id = bom1.id
INNER JOIN t_3de_dbom bom
ON bom.id = bom1.id
INSERT INTO [dbo].[t_3de_dbom_his]
([id],
[structure_level],
[parent_code],
[sub_code],
[sub_name],
[specification],
[number],
[version_number],
[state_index],
[state],
[linenum],
[accession_number],
[bit_number],
[remark],
[create_time])
SELECT bom.[id],
bom.[structure_level],
bom.[parent_code],
bom.[sub_code],
bom.[sub_name],
bom.[specification],
bom.[number],
bom.[version_number],
bom.[state_index],
bom.[state],
bom.[linenum],
bom.[accession_number],
bom.[bit_number],
bom.[remark],
bom.[create_time]
FROM #t_3de_dbom_1 bom1
INNER JOIN t_3de_dbom bom
ON bom.id = bom1.id
WHERE NOT EXISTS(SELECT 1
FROM t_3de_dbom_his h
WHERE h.id = bom1.id)
--DELETE FROM [dbo].[t_3de_dbom]
--WHERE id IN (SELECT id
-- FROM #t_3de_dbom_1)
END
IF Object_id('TEMPDB..#t_3de_dbom_1') IS NOT NULL
TRUNCATE TABLE #t_3de_dbom_1
END
FETCH next FROM order_cursor INTO @id,
@level,
@parent_code,
@sub_code,
@version,
@number,
@remark,
@createtime --转到下一个游标，没有会死循环
END
CLOSE order_cursor --关闭游标
DEALLOCATE order_cursor --释放游标
END2.exec高阶应用、临时表等
CREATE PROCEDURE Proc_lotimportecninfonew(@TableName VARCHAR(100))
AS
BEGIN
DECLARE @sql VARCHAR(1000)
DECLARE @qty DECIMAL(18, 0)
DECLARE @Count INT
DECLARE @mid INT
DECLARE @XH INT
DECLARE @FITEMID INT
IF Object_id('TEMPDB..#EcnImportInfo') IS NOT NULL
DROP TABLE #EcnImportInfo;
CREATE TABLE #EcnImportInfo
(
--id BIGINT IDENTITY(1, 1) NOT NULL,
xc INT,
xh VARCHAR(50),
wlbh1 VARCHAR(50),
wlbh VARCHAR(50),
wlname VARCHAR(max),
ggxh VARCHAR(max),
ggly VARCHAR(max),
ggyy VARCHAR(max),
bgsx VARCHAR(50),
sjtz VARCHAR(30),
wlyn VARCHAR(30),
bb1 VARCHAR(30),
bb2 VARCHAR(30),
xqsl FLOAT,
qgsl FLOAT,
slbk VARCHAR(50),
kcky VARCHAR(30),
kcxg VARCHAR(30),
kczf VARCHAR(30),
gslb VARCHAR(30),
khxc VARCHAR(30),
jjcd VARCHAR(30),
zrbm VARCHAR(50),
bk VARCHAR(1000),
zrr VARCHAR(max),
jsyycwlb VARCHAR(50),
sparePartsProject VARCHAR(4),
oldwlno VARCHAR(30),
ztqty VARCHAR(50),
xmh VARCHAR(30),
zkqty VARCHAR(50),
zzqty VARCHAR(50),
yfhqty VARCHAR(50),
childwlno VARCHAR(30),
childwlmc VARCHAR(100)
)
SET @sql = ' insert into #EcnImportInfo select * from '
+ Quotename(@TableName)
EXEC(@sql)
SELECT @Count = Count(*)
FROM #EcnImportInfo
WHERE Isnull(xqsl, 0) < 0
AND ( Substring(Isnull(childwlno, wlbh), 1, 1) = '5'
OR Substring(Isnull(childwlno, wlbh), 1, 5) = '25001' )
--RETURN;
IF @Count = 0
RETURN;
SELECT *
INTO #tmpecnimportinfo
FROM #EcnImportInfo
TRUNCATE TABLE #EcnImportInfo
--申明游标为fitemid
DECLARE order_cursor CURSOR FOR
(SELECT t.fitemid,
ecn.xc,
Isnull(ecn.xqsl, 0) xqsl
FROM #tmpecnimportinfo ecn
LEFT JOIN t_icitem t
ON CASE Isnull(ecn.childwlno, '')
WHEN '' THEN ecn.wlbh
ELSE ecn.childwlno
END = t.fnumber
WHERE t.fdeleted = 0)
--打开游标--
OPEN order_cursor
--开始循环游标变量--
FETCH next FROM order_cursor INTO @FITEMID, @XH, @qty
WHILE @@FETCH_STATUS = 0 --返回被 FETCH语句执行的最后游标的状态--
BEGIN
SELECT @mid = Isnull(Max(xc), 0) + 1
FROM #EcnImportInfo;
INSERT INTO #EcnImportInfo
([xc],
[xh],
[wlbh1],
[wlbh],
[wlname],
[ggxh],
[ggly],
[ggyy],
[bgsx],
[sjtz],
[wlyn],
[bb1],
[bb2],
[xqsl],
[qgsl],
[slbk],
[kcky],
[kcxg],
[kczf],
[gslb],
[khxc],
[jjcd],
[zrbm],
[bk],
[zrr],
[jsyycwlb],
[sparePartsProject],
[oldwlno],
[ztqty],
[xmh],
[zkqty],
[zzqty],
[yfhqty],
[childwlno],
[childwlmc])
SELECT @mid,
[xh],
[wlbh1],
[wlbh],
[wlname],
[ggxh],
[ggly],
[ggyy],
[bgsx],
[sjtz],
[wlyn],
[bb1],
[bb2],
[xqsl],
[qgsl],
[slbk],
[kcky],
[kcxg],
[kczf],
[gslb],
[khxc],
[jjcd],
[zrbm],
[bk],
[zrr],
[jsyycwlb],
[sparePartsProject],
[oldwlno],
[ztqty],
[xmh],
[zkqty],
[zzqty],
[yfhqty],
[childwlno],
[childwlmc]
FROM #tmpecnimportinfo
WHERE xc = @XH;
IF @qty < 0
BEGIN
WITH temp
AS (SELECT i.FInterID,
i.FItemID AS parent,
ib.FItemID,
ib.FPositionNo,
Cast(Round(CASE
WHEN ib.FQty <= 0.01 THEN 0.01
ELSE ib.FQty
END, 6) AS FLOAT) FQty,--Cast(ib.FQty AS DECIMAL)
1 AS level,
ib.FUnitID,
ib.FBrNo,
i.FParentID,
Cast(t.FShortNumber AS VARCHAR(2000)) bompath
FROM icbom i
INNER JOIN ICBOMChild ib
ON ib.FInterID = i.FInterID
INNER JOIN t_ICItem t
ON ib.FItemID = t.FItemID
WHERE i.FUseStatus = 1072
AND i.FItemID = @FITEMID
UNION ALL
SELECT a.FInterID,
a.parent,
a.FItemID,
a.FPositionNo,
Cast(Round(CASE
WHEN a.FQty * b.FQty <= 0.01 THEN 0.01
ELSE a.FQty * b.FQty
END, 6) AS FLOAT) FQty,
b.level + 1 level,
a.FUnitID,
a.FBrNo,
a.FParentID,
Cast(b.bompath + '->' + a.bompath AS VARCHAR(2000)) bompath
FROM (SELECT i.FInterID,
i.FItemID AS parent,
ib.FItemID,
ib.FPositionNo,
ib.FUnitID,
Round(ib.FQty, 6) FQty,
ib.FBrNo,
i.FParentID,
Cast(t.FShortNumber AS VARCHAR(2000)) bompath
FROM icbom i
INNER JOIN ICBOMChild ib
ON ib.FInterID = i.FInterID
INNER JOIN t_ICItem t
ON ib.FItemID = t.FItemID
WHERE i.FUseStatus = 1072)a
INNER JOIN temp b
ON a.parent = b.FItemID)
INSERT INTO #EcnImportInfo
([xc],
[xh],
[wlbh1],
[wlbh],
[wlname],
[ggxh],
[ggly],
[ggyy],
[bgsx],
[sjtz],
[wlyn],
[bb1],
[bb2],
[xqsl],
[qgsl],
[slbk],
[kcky],
[kcxg],
[kczf],
[gslb],
[khxc],
[jjcd],
[zrbm],
[bk],
[zrr],
[jsyycwlb],
[sparePartsProject],
[oldwlno],
[ztqty],
[xmh],
[zkqty],
[zzqty],
[yfhqty],
[childwlno],
[childwlmc])
SELECT Row_number() OVER( ORDER BY B.fnumber)
+ @mid,
'子级',
--A.[xh],
A.[wlbh1],
B.fnumber,
B.fname,
'',
--A.[ggxh],
A.[ggly],
A.[ggyy],
A.[bgsx],
A.[sjtz],
A.[wlyn],
A.[bb1],
A.[bb2],
B.FQty,
A.[qgsl],
A.[slbk],
A.[kcky],
A.[kcxg],
A.[kczf],
A.[gslb],
A.[khxc],
A.[jjcd],
A.[zrbm],
A.[bk],
A.[zrr],
A.[jsyycwlb],
A.[sparePartsProject],
'',
--A.[oldwlno],
A.[ztqty],
A.[xmh],
A.[zkqty],
A.[zzqty],
A.[yfhqty],
B.fnumber,
B.fname
FROM (SELECT i.fnumber,
i.fname,
Sum(t.FQty) * @qty FQty,
'ttt' keyvalue
FROM temp t
INNER JOIN t_icitem i
ON t.FItemID = i.FItemID
GROUP BY i.fnumber,
i.fname)B
INNER JOIN (SELECT *,
'ttt' keyvalue
FROM #tmpecnimportinfo tmp
WHERE xc = @XH) A
ON A.keyvalue = B.keyvalue
END
FETCH next FROM order_cursor INTO @FITEMID, @XH, @qty --转到下一个游标，没有会死循环
END
CLOSE order_cursor --关闭游标
DEALLOCATE order_cursor --释放游标
SET @sql = ' begin ' + ' delete from '
+ Quotename(@TableName) + ';' + ' insert into '
+ Quotename(@TableName)
+ ' select * from #EcnImportInfo ; '
+ ' end '
EXEC(@sql)
END3.创建动态列
1.创建分割函数splitstr
CREATE FUNCTION [dbo].[Uf_splitstringtotable](@sInputList VARCHAR(MAX) -- List of delimited items
,
@sDelimiter VARCHAR(20) = ',' -- delimiter that separates items
)
RETURNS @List TABLE (
item VARCHAR(50))
BEGIN
DECLARE @sItem VARCHAR(MAX)
WHILE Charindex(@sDelimiter, @sInputList, 0) <> 0
BEGIN
SELECT @sItem = Rtrim(Ltrim(Substring(@sInputList, 1, Charindex(@sDelimiter, @sInputList, 0) - 1))),
@sInputList = Rtrim(Ltrim(Substring(@sInputList, Charindex(@sDelimiter, @sInputList, 0)
+ Len(@sDelimiter), Len(@sInputList))))
IF Len(@sItem) > 0
INSERT INTO @List
SELECT @sItem
END
IF Len(@sInputList) > 0
INSERT INTO @List
SELECT @sInputList -- Put the last item in
RETURN
END
2.Sp_executesql高阶用法
CREATE PROCEDURE Proc_bomdiffanalyse1(@zjbm VARCHAR(500),
@return VARCHAR(500) OUTPUT)
AS
BEGIN
IF Object_id('TEMPDB..#t_bomdifferent') IS NOT NULL
DROP TABLE #t_bomdifferent;
CREATE TABLE #t_bomdifferent
(
fnumber VARCHAR(50),
bom1_qty DECIMAL(18, 4),
bom1_count INT,
bom2_qty DECIMAL(18, 4),
bom2_count INT,
bom3_qty DECIMAL(18, 4),
bom3_count INT,
bom4_qty DECIMAL(18, 4),
bom4_count INT,
bom5_qty DECIMAL(18, 4),
bom5_count INT,
bom6_qty DECIMAL(18, 4),
bom6_count INT,
bom7_qty DECIMAL(18, 4),
bom7_count INT,
bom8_qty DECIMAL(18, 4),
bom8_count INT,
bom9_qty DECIMAL(18, 4),
bom9_count INT,
bom10_qty DECIMAL(18, 4),
bom10_count INT
)
DECLARE @NUM INT=0,
@xh INT=0,
@FInterID INT=0
DECLARE @fnumber VARCHAR(500)
DECLARE @bom1_qty VARCHAR(500)='BOM1_用量'
DECLARE @bom1_count VARCHAR(500)='BOM1_行数'
DECLARE @bom2_qty VARCHAR(500)='BOM2_用量'
DECLARE @bom2_count VARCHAR(500)='BOM2_行数'
DECLARE @bom3_qty VARCHAR(500)='BOM3_用量'
DECLARE @bom3_count VARCHAR(500)='BOM3_行数'
DECLARE @bom4_qty VARCHAR(500)='BOM4_用量'
DECLARE @bom4_count VARCHAR(500)='BOM4_行数'
DECLARE @bom5_qty VARCHAR(500)='BOM5_用量'
DECLARE @bom5_count VARCHAR(500)='BOM5_行数'
DECLARE @bom6_qty VARCHAR(500)='BOM6_用量'
DECLARE @bom6_count VARCHAR(500)='BOM6_行数'
DECLARE @bom7_qty VARCHAR(500)='BOM7_用量'
DECLARE @bom7_count VARCHAR(500)='BOM7_行数'
DECLARE @bom8_qty VARCHAR(500)='BOM8_用量'
DECLARE @bom8_count VARCHAR(500)='BOM8_行数'
DECLARE @bom9_qty VARCHAR(500)='BOM9_用量'
DECLARE @bom9_count VARCHAR(500)='BOM9_行数'
DECLARE @bom10_qty VARCHAR(500)='BOM10_用量'
DECLARE @bom10_count VARCHAR(500)='BOM10_行数'
DECLARE @Sql NVARCHAR(MAX);
SELECT @NUM = Count(*)
FROM dbo.Uf_splitstringtotable(@zjbm, ',') i
IF @NUM > 10
BEGIN
SET @return='BOM比较功能最多支持5个'
RETURN
END
SELECT TOP 1 @fnumber = i.item
FROM dbo.Uf_splitstringtotable(@zjbm, ',') i
LEFT JOIN t_ICItem t
ON i.item = t.fnumber
WHERE t.fitemid IS NULL
IF @fnumber IS NOT NULL
BEGIN
SET @return='输入的物料编码【' + @fnumber + '】不存在'
RETURN
END
DECLARE order_cursor CURSOR FOR
(SELECT i.item
FROM dbo.Uf_splitstringtotable(@zjbm, ',') i)
--打开游标--
OPEN order_cursor
--开始循环游标变量--
FETCH next FROM order_cursor INTO @fnumber
WHILE @@FETCH_STATUS = 0 --返回被 FETCH语句执行的最后游标的状态--
BEGIN
SET @return=''
SET @xh=@xh + 1
IF Object_id('TEMPDB..#t_temp_bomdifferent') IS NOT NULL
DROP TABLE #t_temp_bomdifferent;
BEGIN TRY
--SELECT @FInterID = FInterID
--FROM ICBOMGroup
--WHERE FNumber = @fnumber;
SELECT @FInterID = fitemid
FROM t_ICItem t
WHERE t.FNumber = @fnumber;
WITH temp
AS (SELECT i.FInterID,
i.FItemID AS parent,
ib.FItemID,
ib.FPositionNo,
Cast(ib.Fauxqty AS DECIMAL(18, 4)) FQty,
1 AS level,
ib.FUnitID,
ib.FBrNo,
i.FParentID,
Cast(ib.FItemID AS VARCHAR(2000)) bompath
FROM icbom i
INNER JOIN ICBOMChild ib
ON ib.FInterID = i.FInterID
WHERE i.FUseStatus = 1072
AND i.FItemID = @FInterID
UNION ALL
SELECT a.FInterID,
a.parent,
a.FItemID,
a.FPositionNo,
Cast(a.FQty * b.FQty AS DECIMAL(18, 4)) FQty,
b.level + 1 level,
a.FUnitID,
a.FBrNo,
a.FParentID,
Cast(b.bompath + '->' + a.bompath AS VARCHAR(2000)) bompath
FROM (SELECT i.FInterID,
i.FItemID AS parent,
ib.FItemID,
ib.FPositionNo,
ib.FUnitID,
Cast(ib.Fauxqty AS DECIMAL(18, 4)) FQty,
ib.FBrNo,
i.FParentID,
Cast(ib.FItemID AS VARCHAR(2000)) bompath
FROM icbom i
INNER JOIN ICBOMChild ib
ON ib.FInterID = i.FInterID
WHERE i.FUseStatus = 1072)a
INNER JOIN temp b
ON a.parent = b.FItemID)
SELECT i.fnumber,
Sum(Isnull(FQty, 0)) AS FQty,
Count(*) AS Facc
INTO #t_temp_bomdifferent
FROM temp t
LEFT JOIN t_ICItem i
ON i.fitemid = t.fitemid
GROUP BY i.fnumber
IF @xh = 1
BEGIN
SET @bom1_qty=@fnumber + '_用量'
SET @bom1_count=@fnumber + '_行数'
INSERT INTO #t_bomdifferent
(fnumber,
bom1_qty,
bom1_count)
SELECT *
FROM #t_temp_bomdifferent
END
ELSE IF @xh = 2
BEGIN
SET @bom2_qty=@fnumber + '_用量'
SET @bom2_count=@fnumber + '_行数'
UPDATE t
SET t.bom2_qty = tmp.FQty,
t.bom2_count = tmp.Facc
FROM #t_bomdifferent t
INNER JOIN #t_temp_bomdifferent tmp
ON t.fnumber = tmp.fnumber
INSERT INTO #t_bomdifferent
(fnumber,
bom2_qty,
bom2_count)
SELECT *
FROM #t_temp_bomdifferent tmp
WHERE NOT EXISTS(SELECT 1
FROM #t_bomdifferent t
WHERE t.fnumber = tmp.fnumber)
END
ELSE IF @xh = 3
BEGIN
SET @bom3_qty=@fnumber + '_用量'
SET @bom3_count=@fnumber + '_行数'
UPDATE t
SET t.bom3_qty = tmp.FQty,
t.bom3_count = tmp.Facc
FROM #t_bomdifferent t
INNER JOIN #t_temp_bomdifferent tmp
ON t.fnumber = tmp.fnumber
INSERT INTO #t_bomdifferent
(fnumber,
bom3_qty,
bom3_count)
SELECT *
FROM #t_temp_bomdifferent tmp
WHERE NOT EXISTS(SELECT 1
FROM #t_bomdifferent t
WHERE t.fnumber = tmp.fnumber)
END
ELSE IF @xh = 4
BEGIN
SET @bom4_qty=@fnumber + '_用量'
SET @bom4_count=@fnumber + '_行数'
UPDATE t
SET t.bom4_qty = tmp.FQty,
t.bom4_count = tmp.Facc
FROM #t_bomdifferent t
INNER JOIN #t_temp_bomdifferent tmp
ON t.fnumber = tmp.fnumber
INSERT INTO #t_bomdifferent
(fnumber,
bom4_qty,
bom4_count)
SELECT *
FROM #t_temp_bomdifferent tmp
WHERE NOT EXISTS(SELECT 1
FROM #t_bomdifferent t
WHERE t.fnumber = tmp.fnumber)
END
ELSE IF @xh = 5
BEGIN
SET @bom5_qty=@fnumber + '_用量'
SET @bom5_count=@fnumber + '_行数'
UPDATE t
SET t.bom5_qty = tmp.FQty,
t.bom5_count = tmp.Facc
FROM #t_bomdifferent t
INNER JOIN #t_temp_bomdifferent tmp
ON t.fnumber = tmp.fnumber
INSERT INTO #t_bomdifferent
(fnumber,
bom5_qty,
bom5_count)
SELECT *
FROM #t_temp_bomdifferent tmp
WHERE NOT EXISTS(SELECT 1
FROM #t_bomdifferent t
WHERE t.fnumber = tmp.fnumber)
END
ELSE IF @xh = 6
BEGIN
SET @bom6_qty=@fnumber + '_用量'
SET @bom6_count=@fnumber + '_行数'
UPDATE t
SET t.bom6_qty = tmp.FQty,
t.bom6_count = tmp.Facc
FROM #t_bomdifferent t
INNER JOIN #t_temp_bomdifferent tmp
ON t.fnumber = tmp.fnumber
INSERT INTO #t_bomdifferent
(fnumber,
bom6_qty,
bom6_count)
SELECT *
FROM #t_temp_bomdifferent tmp
WHERE NOT EXISTS(SELECT 1
FROM #t_bomdifferent t
WHERE t.fnumber = tmp.fnumber)
END
ELSE IF @xh = 7
BEGIN
SET @bom7_qty=@fnumber + '_用量'
SET @bom7_count=@fnumber + '_行数'
UPDATE t
SET t.bom7_qty = tmp.FQty,
t.bom7_count = tmp.Facc
FROM #t_bomdifferent t
INNER JOIN #t_temp_bomdifferent tmp
ON t.fnumber = tmp.fnumber
INSERT INTO #t_bomdifferent
(fnumber,
bom7_qty,
bom7_count)
SELECT *
FROM #t_temp_bomdifferent tmp
WHERE NOT EXISTS(SELECT 1
FROM #t_bomdifferent t
WHERE t.fnumber = tmp.fnumber)
END
ELSE IF @xh = 8
BEGIN
SET @bom8_qty=@fnumber + '_用量'
SET @bom8_count=@fnumber + '_行数'
UPDATE t
SET t.bom8_qty = tmp.FQty,
t.bom8_count = tmp.Facc
FROM #t_bomdifferent t
INNER JOIN #t_temp_bomdifferent tmp
ON t.fnumber = tmp.fnumber
INSERT INTO #t_bomdifferent
(fnumber,
bom8_qty,
bom8_count)
SELECT *
FROM #t_temp_bomdifferent tmp
WHERE NOT EXISTS(SELECT 1
FROM #t_bomdifferent t
WHERE t.fnumber = tmp.fnumber)
END
ELSE IF @xh = 9
BEGIN
SET @bom9_qty=@fnumber + '_用量'
SET @bom9_count=@fnumber + '_行数'
UPDATE t
SET t.bom9_qty = tmp.FQty,
t.bom9_count = tmp.Facc
FROM #t_bomdifferent t
INNER JOIN #t_temp_bomdifferent tmp
ON t.fnumber = tmp.fnumber
INSERT INTO #t_bomdifferent
(fnumber,
bom9_qty,
bom9_count)
SELECT *
FROM #t_temp_bomdifferent tmp
WHERE NOT EXISTS(SELECT 1
FROM #t_bomdifferent t
WHERE t.fnumber = tmp.fnumber)
END
ELSE IF @xh = 10
BEGIN
SET @bom10_qty=@fnumber + '_用量'
SET @bom10_count=@fnumber + '_行数'
UPDATE t
SET t.bom10_qty = tmp.FQty,
t.bom10_count = tmp.Facc
FROM #t_bomdifferent t
INNER JOIN #t_temp_bomdifferent tmp
ON t.fnumber = tmp.fnumber
INSERT INTO #t_bomdifferent
(fnumber,
bom10_qty,
bom10_count)
SELECT *
FROM #t_temp_bomdifferent tmp
WHERE NOT EXISTS(SELECT 1
FROM #t_bomdifferent t
WHERE t.fnumber = tmp.fnumber)
END
END TRY
BEGIN CATCH
SELECT @return = '数据出现异常，回滚事务。具体原因：' + Error_message()
END CATCH
FETCH next FROM order_cursor INTO @fnumber --转到下一个游标，没有会死循环
END
CLOSE order_cursor --关闭游标
DEALLOCATE order_cursor --释放游标
--SELECT t.fnumber 物料编码,
-- i.FName 物料名称,
-- i.FModel 物料型号,
-- i.F_118 物料品牌,
-- t.bom1_qty BOM1_用量,
-- t.bom1_count BOM1_行数,
-- t.bom2_qty BOM2_用量,
-- t.bom2_count BOM2_行数,
-- t.bom3_qty BOM3_用量,
-- t.bom3_count BOM3_行数,
-- t.bom4_qty BOM4_用量,
-- t.bom4_count BOM4_行数,
-- t.bom5_qty BOM5_用量,
-- t.bom5_count BOM5_行数,
-- t.bom6_qty BOM6_用量,
-- t.bom6_count BOM6_行数,
-- t.bom7_qty BOM7_用量,
-- t.bom7_count BOM7_行数,
-- t.bom8_qty BOM8_用量,
-- t.bom8_count BOM8_行数,
-- t.bom9_qty BOM9_用量,
-- t.bom9_count BOM9_行数,
-- t.bom10_qty BOM10_用量,
-- t.bom10_count BOM10_行数
--FROM #t_bomdifferent t
-- LEFT JOIN t_ICItem i
-- ON t.fnumber = i.fnumber
--ORDER BY t.fnumber
SET @Sql=N' SELECT t.fnumber 物料编码,
i.FName 物料名称,
i.FModel 物料型号,
i.F_118 物料品牌,
t.bom1_qty ''' + @bom1_qty
+ ''',
t.bom1_count '''
+ @bom1_count
+ ''',
t.bom2_qty ''' + @bom2_qty
+ ''',
t.bom2_count '''
+ @bom2_count
+ ''',
t.bom3_qty ''' + @bom3_qty
+ ''',
t.bom3_count '''
+ @bom3_count
+ ''',
t.bom4_qty ''' + @bom4_qty
+ ''',
t.bom4_count '''
+ @bom4_count
+ ''',
t.bom5_qty ''' + @bom5_qty
+ ''',
t.bom5_count '''
+ @bom5_count
+ ''',
t.bom6_qty ''' + @bom6_qty
+ ''',
t.bom6_count '''
+ @bom6_count
+ ''',
t.bom7_qty ''' + @bom7_qty
+ ''',
t.bom7_count '''
+ @bom7_count
+ ''',
t.bom8_qty ''' + @bom8_qty
+ ''',
t.bom8_count '''
+ @bom8_count
+ ''',
t.bom9_qty ''' + @bom9_qty
+ ''',
t.bom9_count '''
+ @bom9_count
+ ''',
t.bom10_qty ''' + @bom10_qty
+ ''',
t.bom10_count '''
+ @bom10_count + '''
FROM #t_bomdifferent t
LEFT JOIN t_ICItem i
ON t.fnumber = i.fnumber
ORDER BY t.fnumber '
EXEC Sp_executesql
@Sql;
END
947
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
