sqlserver 存储过程例子
CREATE PROCEDURE CustomerTotal
@CustomerID int,
@BeginDate Datetime,
@EndDate Datetime
AS
Begin
Set NoCount On
Declare @CustomerStockIO Table --临时表
(
fDate Datetime,
fNote nvarchar(10),
fNO nvarchar(20),
fFlag int not null default 0,
fProductID int,
fQty numeric(10,2),
fUnitPrice numeric(10,2)
)
Insert Into @CustomerStockIO
Select a.fSubmitDate,'出货',a.fNO,0,b.fResID,b.fQty,b.fUnitPrice
from tSellOut a,tSellOutSub b
where a.fID=b.fID
and a.fCustomerID=@CustomerID
and a.fSubmitDate>=@BeginDate
and a.fSubmitDate<=@EndDate
Insert Into @CustomerStockIO
Select a.fSubmitDate,'退货',a.fNO,1,b.fResID,-b.fQty,b.fUnitPrice
from tSellBack a,tSellBackSub b
where a.fID=b.fID
and a.fCustomerID=@CustomerID
and a.fSubmitDate>=@BeginDate
and a.fSubmitDate<=@EndDate
Select fProductID,sum(fQty)as fQty,sum(fUnitPrice)as fUnitPrice, sum(fQty*fUnitPrice)as fSum
from @CustomerStockIO
group by fProductID
order by fProductID
Set NoCount OFF
END
***********************************************
得到用到当前物件的产品列表
作者：姜玉龙
日期：2002.06.13
***********************************************/
CREATE PROCEDURE pBomParentList @ProductID Int
AS
Begin
Declare @LevelCount Int --级数
Declare @Tmp Table --临时表
(
fResID int not null
)
Declare @TmpA Table --临时表A
(
fResID int not null
)
Declare @TmpB Table --临时表B
(
fResID int not null
)
Insert into @TmpA
Select fParentID from tBom where fPartID=@ProductID
While (Select Count(*) from @TmpA)>0
begin
If @LevelCount>20
Goto Out
Insert Into @Tmp Select * from @TmpA
Delete From @TmpB
Insert Into @TmpB Select * from @TmpA
Delete From @TmpA
Insert Into @TmpA
Select fParentID from tBom Where fPartID in (Select fResID from @TmpB)
Set @LevelCount=@LevelCount+1
end
Out:
Select Distinct fResID from @Tmp
End
***********************************************
检验BOM中子件的有效性
0，成功；
1，已存在该子件；
2，当前子件是当前父件的父类产品
作者：姜玉龙
日期：2002.06.10
***********************************************/
CREATE PROCEDURE pBomPartChedk
@ParentID Int, --父件ID
@PartID Int --子件ID
AS
Begin
Declare @LevelCount Int --级数
Declare @Tmp Table --临时表
(
fResID int not null
)
Declare @TmpA Table --临时表A
(
fResID int not null
)
Declare @TmpB Table --临时表B
(
fResID int not null
)
if exists(Select * from tBom where fParentID=@ParentID and fPartID=@PartID)
Return 1
Insert into @TmpA
Select fParentID from tBom where fPartID=@ParentID
While (Select Count(*) from @TmpA)>0
begin
If @LevelCount>20
Goto Out
Insert Into @Tmp Select * from @TmpA
if exists(Select * from @Tmp where fResID=@PartID)
Return 2
Delete From @TmpB
Insert Into @TmpB Select * from @TmpA
Delete From @TmpA
Insert Into @TmpA
Select fParentID from tBom Where fPartID in (Select fResID from @TmpB)
Set @LevelCount=@LevelCount+1
end
Out:
Return 0
/*
建立物料BOM清单
*/
CREATE PROCEDURE pBOMPartList(@intProdID int)
AS
Declare @LevCount Integer
set nocount on
Select @LevCount=1
select fPartID,fQty into #dBOMA from tBOM where fParentID=@intProdID
select fParentID,fPartID,fQty
into #dBOM from tBOM
where fParentID=@intProdID
Insert #dBOM(fParentID,fPartID,fQty) values(0,@IntProdID,1)-----将原产品加入
select fPartID=@intProdID into #dBOMB
while (select count(*) from #dBOMA)>0
begin
if (@LevCount>20)
begin
goto Out
end
Insert #dBOM(fParentID,fPartID,fQty)
select a.fParentID,a.fPartID,a.fQty*b.fQty
from tBOM a,#dBomA b
where a.fParentID=b.fPartID
delete from #dBOMB
insert #dBOMB select fPartID from #dBOMA
delete from #dBOMA
insert #dBOMA
select fPartID,fQty from #dBOM
where fParentID in (select fPartID from #dBOMB)
Delete from #dBOMB
Select @LevCount=@LevCount+1
end
out:
select fPartID as fID,sum(fQty) fQty from #dBOM group by fPartID
set nocount off
*
建立物料BOM清单
*/
CREATE PROCEDURE pBOMTreeView(@intProdID int)
AS
Begin
Declare @LevCount Integer
set nocount on
Select @LevCount=1
select fPartID into #dBOMA from tBOM where fParentID=@intProdID
select fParentID,fPartID,name=rtrim(ltrim(IsNull(tProduct.fCode,'')))+' '+rtrim(ltrim(IsNull(tProduct.fName,''))),fQty
into #dBOM from tBOM,tProduct
where fParentID=@intProdID and tProduct.fID=tBOM.fPartID
Insert #dBOM(fParentID,fPartID,name,fQty)-----将原产品加入
select fParentID=0,fPartID=@intProdID,name=rtrim(ltrim(IsNull(fCode,'')))+' '+rtrim(ltrim(IsNull(tProduct.fName,''))),1
from tProduct
where fID=@intProdID
select fPartID=@intProdID into #dBOMB
while (select count(*) from #dBOMA)>0
begin
if (@LevCount>20)
begin
goto Out
end
Insert #dBOM(fParentID,fPartID,name,fQty)
select fParentID,fPartID,name=rtrim(ltrim(IsNull(tProduct.fCode,'')))+' '+rtrim(ltrim(IsNull(tProduct.fName,''))),fQty
from tBOM,tProduct
where fParentID in (select fPartID from #dBOMA) and tBOM.fPartID=tProduct.fID
delete from #dBOMB
insert #dBOMB select fPartID from #dBOMA
delete from #dBOMA
insert #dBOMA
select fPartID from tBOM
where fParentID in (select fPartID from #dBOMB)
Delete from #dBOMB
Select @LevCount=@LevCount+1
end
out:
--select distinct * from #dBOM
select a.fParentID as PID,
a.fPartID as AID,a.Name as AName,a.fQty as AQty,
b.fPartID as BID,b.Name as BName,b.fQty as BQty,
c.fPartID as CID,c.Name as CName,c.fQty as CQty,
d.fPartID as DID,d.Name as DName,d.fQty as DQty,
e.fPartID as EID,e.Name as EName,e.fQty as EQty,
f.fPartID as FID,f.Name as FName,f.fQty as FQty,
g.fPartID as GID,g.Name as GName,g.fQty as GQty,
h.fPartID as HID,h.Name as HName,h.fQty as HQty
from #dBOM as a
left join #dBOM as b on a.fPartID=b.fParentID
left join #dBOM as c on b.fPartID=c.fParentID
left join #dBOM as d on c.fPartID=d.fParentID
left join #dBOM as e on d.fPartID=e.fParentID
left join #dBOM as f on e.fPartID=f.fParentID
left join #dBOM as g on e.fPartID=f.fParentID
left join #dBOM as h on e.fPartID=f.fParentID
where a.fParentID=@intProdID
set nocount off
End
/*
增加工厂日历
*/
CREATE PROCEDURE pCalendarEdit(
@intType int--增加类型。@intType=1批增；@intType=2年增；@intType=3批删。
,@datBeginDate datetime
,@datEndDate datetime
,@isSaturDay bit
,@isSunDay bit
,@numMaxTime numeric(8,2)
,@numWorkTime numeric(8,2))
AS
set NoCount on
begin tran
if @intType=1
begin
declare @datCurDate datetime
select @datCurDate =@datBeginDate
--删除已经存在的
Delete from tCalendar where fDay>=@datBeginDate and fDay<=@datEndDate
while @datCurDate<=@datEndDate
begin
insert into tCalendar(fDay,fYear,fMonth,fMaxTime,fWorkTime,fUnit,fActYear,fActMonth)
values(@datCurDate,Year(@datCurDate),Month(@datCurDate),@numMaxTime,@numWorkTime,'HRS',Year(@datCurDate),Month(@datCurDate))
select @datCurDate=@datCurDate+1
end
if @isSaturDay=1
begin
update tCalendar set fWorkTime=0 where fDay>=@datBeginDate and fDay<=@datEndDate and DATEPART(dw, fDay)=7
end
if @isSunDay=1
begin
update tCalendar set fWorkTime=0 where fDay>=@datBeginDate and fDay<=@datEndDate and DATEPART(dw,fDay)=1
end
end
else if @intType=2--年增
begin
declare @intYear int
--删除已经存在的
Delete from tCalendar where year(fDay)=year(@datEndDate)
select @intYear=Year(@datEndDate)-Year(@datBeginDate)
insert into tCalendar(fDay,fYear,fMonth,fMaxTime,fWorkTime,fUnit,fActYear,fActMonth)
select DateAdd(year,@intYear,fDay),fYear,fMonth,fMaxTime,fWorkTime,fUnit,fActYear,fActMonth from tCalendar where Year(fDay)=Year(@datBeginDate)
end
else if @intType=3
begin
Delete from tCalendar where fDay>=@datBeginDate and fDay<=@datEndDate
end
if @@error<>0
begin
评论0