sqlserver存储过程test
发布时间：2020-12-12 14:18:54 所属栏目：MsSql教程 来源：网络整理
导读：IF EXISTS ( SELECT * FROM sysobjects WHERE name = ‘pro_reduce_stock‘ ) DROP PROCEDURE pro_reduce_stock GO CREATE PROCEDURE pro_reduce_stock ? @wareHouseId bigint, ? @detailId bigint, ? @detailNum INT AS BEGIN BEGIN TRANSACTION BEGIN try
|
IF EXISTS (
SELECT
*
FROM
sysobjects
WHERE
name = ‘pro_reduce_stock‘
) DROP PROCEDURE pro_reduce_stock
GO
CREATE PROCEDURE pro_reduce_stock
? @wareHouseId bigint,
? @detailId bigint,
? @detailNum INT
AS
BEGIN
BEGIN TRANSACTION
BEGIN try
? ? ? --扣减物料?
CREATE TABLE #bomTemp
(
id INT IDENTITY(1,1),
product_detail_id bigint,
item_id bigint,
item_num INT
)
INSERT #bomTemp(product_detail_id,item_id,item_num) SELECT product_detail_id,item_num FROM op_product_bom WHERE
[email?protected]
DECLARE @currentIndex INT??
DECLARE @totalRows? ? INT??
SELECT @currentIndex=1??
SELECT @totalRows=COUNT(1) FROM #bomTemp
DECLARE @materielId INT??
DECLARE @materielNum INT
SELECT @materielId=0??
SELECT @materielNum=0??
WHILE(@currentIndex<
[email?protected])??
BEGIN? ?
SELECT @materielId=item_id FROM #bomTemp WHERE
[email?protected]
SELECT @materielNum=item_num FROM #bomTemp WHERE
[email?protected]??
--UPDATE scm_stock_num SET
[email?protected]*@materielNum WHERE
[email?protected] AND
[email?protected]
SELECT @
[email?protected]+1;??
END??
? ? ? --扣减商品明细
? ? ? --UPDATE scm_stock_num SET
[email?protected] WHERE
[email?protected] AND
[email?protected]
COMMIT TRANSACTION
END try
BEGIN catch
ROLLBACK TRANSACTION
END catch
END
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |