MSSQL2000如下存储过程每次运行一个货品GOODSID的,并每次把数据写入到MRP_MASTERVip表中。
要求 :修改为无参数的存储过程(goodsid)一次运行可以把所有相关数据写入到MRP_MASTERVip
或如何循环运行该存储过程。
原存储过程如下:
CREATE PROCEDURE MRP_FILLMRPMASTERVip
@GOODSID INT, @RDATE DATETIME, @GROSSQTY FLOAT AS
DECLARE @LASTSTAGE INT, @BOMSOURCEID INT, @BOMSOURCETYPE INT, @ADATE DATETIME
IF (EXISTS (SELECT PARENTGOODSID FROM MRP_MPPLANDEPLOYVip WHERE PARENTGOODSID = @GOODSID))
BEGIN
IF (EXISTS (SELECT PARENTGOODSID FROM MRP_MPPLANDEPLOYVip WHERE GOODSID = @GOODSID AND PARENTGOODSID = 0))
SET @LASTSTAGE = - 1
ELSE
SET @LASTSTAGE = 0
END
ELSE
SET @LASTSTAGE = 1
IF (@LASTSTAGE = 1)
SET @BOMSOURCEID = 2
ELSE
SET @BOMSOURCEID = 1
SELECT @BOMSOURCETYPE = BOMSOURCEID FROM GOODS WHERE GOODSID = @GOODSID
IF ((@BOMSOURCETYPE = 3 OR @BOMSOURCETYPE = 2) AND @LASTSTAGE = 0)
SET @BOMSOURCEID = @BOMSOURCETYPE
--- SELECT @ADATE = RDATE FROM MRP_SELMPPLANVip WHERE GOODSID = @GOODSID
--- IF (@ADATE IS NULL)
SET @ADATE = @RDATE
INSERT INTO MRP_MASTERVip (GOODSID, RDATE, GROSSQTY, PURIFYGROSSQTY, LASTSTAGE, BOMSOURCEID)
VALUES (@GOODSID, @ADATE, @GROSSQTY, @GROSSQTY, @LASTSTAGE, @BOMSOURCEID)
go
其中MRP_MPPLANDEPLOYVip表结构如下: (这里为数据数源)
PARENTGOODSID GOODSID QTY MAXLEVEL ORDERNO ISPARENT CPRICE BOMID其中MRP_MASTERVip表结构如下: (计算写入到目标数据)
GOODSID GROSSQTY ACTIVEQTY READYINQTY SAFEQTY READYOUTQTY READYCHECKQTY
ELIGIBLE CLEANREQUIREQTY LASTSTAGE BOMSOURCEID REVISEQTY MAXLEVEL
USERSET PURIFYGROSSQTY APRICE RDATE
要求 :修改为无参数的存储过程(goodsid)一次运行可以把所有相关数据写入到MRP_MASTERVip
或如何循环运行该存储过程。
原存储过程如下:
CREATE PROCEDURE MRP_FILLMRPMASTERVip
@GOODSID INT, @RDATE DATETIME, @GROSSQTY FLOAT AS
DECLARE @LASTSTAGE INT, @BOMSOURCEID INT, @BOMSOURCETYPE INT, @ADATE DATETIME
IF (EXISTS (SELECT PARENTGOODSID FROM MRP_MPPLANDEPLOYVip WHERE PARENTGOODSID = @GOODSID))
BEGIN
IF (EXISTS (SELECT PARENTGOODSID FROM MRP_MPPLANDEPLOYVip WHERE GOODSID = @GOODSID AND PARENTGOODSID = 0))
SET @LASTSTAGE = - 1
ELSE
SET @LASTSTAGE = 0
END
ELSE
SET @LASTSTAGE = 1
IF (@LASTSTAGE = 1)
SET @BOMSOURCEID = 2
ELSE
SET @BOMSOURCEID = 1
SELECT @BOMSOURCETYPE = BOMSOURCEID FROM GOODS WHERE GOODSID = @GOODSID
IF ((@BOMSOURCETYPE = 3 OR @BOMSOURCETYPE = 2) AND @LASTSTAGE = 0)
SET @BOMSOURCEID = @BOMSOURCETYPE
--- SELECT @ADATE = RDATE FROM MRP_SELMPPLANVip WHERE GOODSID = @GOODSID
--- IF (@ADATE IS NULL)
SET @ADATE = @RDATE
INSERT INTO MRP_MASTERVip (GOODSID, RDATE, GROSSQTY, PURIFYGROSSQTY, LASTSTAGE, BOMSOURCEID)
VALUES (@GOODSID, @ADATE, @GROSSQTY, @GROSSQTY, @LASTSTAGE, @BOMSOURCEID)
go
其中MRP_MPPLANDEPLOYVip表结构如下: (这里为数据数源)
PARENTGOODSID GOODSID QTY MAXLEVEL ORDERNO ISPARENT CPRICE BOMID其中MRP_MASTERVip表结构如下: (计算写入到目标数据)
GOODSID GROSSQTY ACTIVEQTY READYINQTY SAFEQTY READYOUTQTY READYCHECKQTY
ELIGIBLE CLEANREQUIREQTY LASTSTAGE BOMSOURCEID REVISEQTY MAXLEVEL
USERSET PURIFYGROSSQTY APRICE RDATE
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
如果现在的程序是点某个按钮,传入存储过程@GOODSID值!
如果@GOODSID是从其他表里来的值是可以的
是的,@GOODSID是从其他表里来的值
由表FROM MRP_MPPLANDEPLOYVip 传来的
EX:
create proc MRP_xxx()
asdeclare @GOODSID int
...DECLARE tb_cursor CURSOR FOR
SELECT PARENTGOODSID , ...
FROM MRP_MPPLANDEPLOYVipOPEN tb_cursorFETCH NEXT FROM tb_cursor
INTO @GOODSID, ...WHILE @@FETCH_STATUS = 0
begin
execMRP_FILLMRPMASTERVip @GOODSID,...
FETCH NEXT FROM tb_cursor
INTO @GOODSID, ...
end
CLOSE tb_cursor
DEALLOCATE tb_cursor