Spare parts information, then INSERT to the table,
Problem: 4 people in PB execution at the same time, some corresponding drawing no. 15 rows of data information at a lower level of all take to INSERT a success, and some people in the middle part of drawing no. No INSERT success, trouble which expert to help see, sometimes recompense lock process,
SQL stored procedure code:
The SET ANSI_NULLS ON
GO
The SET QUOTED_IDENTIFIER ON
GO
The ALTER PROCEDURE [dbo] [up_getpdm_bom_scwtd_new]
@ STRLDBH VARCHAR (20),
@ STRZJH VARCHAR (50)
AS
SET NOCOUNT ON
- start transaction
The BEGIN TRANSACTION
;
WITH temp_bom_bc (ZJH, LBJTH LBJMC, sx1, WZBH, JGZSL, sumsl)
AS
(SELECT ZJH, LBJTH LBJMC sx1, WZBH, JGZSL, CAST (JGZSL as numeric (16, 4)) as sumsl from WZXT. The dbo. Uv_outerpdm_bom
WHERE ZJH=@ STRZJH AND sx1 IN (' whole ', 'parts',' parts')
UNION ALL
The SELECT Uv_outerpdm_bom. ZJH, Uv_outerpdm_bom LBJTH, Uv_outerpdm_bom. LBJMC, Uv_outerpdm_bom. Sx1, Uv_outerpdm_bom. WZBH,
Uv_outerpdm_bom JGZSL, CAST (temp_bom_bc sumsl * Uv_outerpdm_bom JGZSL as numeric (16, 4))
The FROM temp_bom_bc
The JOIN WZXT. Dbo. Uv_outerpdm_bom as Uv_outerpdm_bom ON temp_bom_bc. LBJTH=Uv_outerpdm_bom. ZJH AND
Uv_outerpdm_bom. Sx1 IN (' whole 'and' parts', 'parts')
WHERE Uv_outerpdm_bom. ZJH=temp_bom_bc. LBJTH
)
INSERT INTO wtd_bjmx (BJMC LDBH, ZJH, BJH, lb, WZBH, JGSL, sl, ZZJH)
SELECT @ STRLDBH temp_bom_bc. ZJH, temp_bom_bc LBJTH, temp_bom_bc. LBJMC, temp_bom_bc. Sx1,
Temp_bom_bc WZBH, JGZSL, sumsl * @ STRXQSL, @ STRZJH
The FROM (SELECT * FROM temp_bom_bc) AS temp_bom_bc LEFT JOIN WZXT. The dbo. Uv_outerzl_bzk AS uv_outerzl_bzk ON uv_outerzl_bzk. WZBH=
Temp_bom_bc. WZBH
WHERE temp_bom_bc. Sx1 IN (' whole ', 'parts',' parts')
/* to commit the transaction */
COMMIT
/* * options/
SET NOCOUNT OFF
The RETURN
CodePudding user response:
The BEGIN of the stored procedure to the TRANSACTION and COMMIT commented out, write TRANSACTION control in the application,