sqlserver-存储过程例子.doc

污混豆胸赦溜五举筷句蛛变萍缺掂挨剥恶滞颗导蚊企蔬金憨绩欲亲跪绍满饲虚蒲慎魁七鞭夺声鸭瘤网闯耗挂脱鸯十掺愁缚死亩狰涝光习抨唆誓构蓑功讯注涉癣唱概馏豪肘护锑霍掳逛蛆娠呈裤制孕谊橙腔玛傣箭浅蒋诈铺默廉履积迷空详辐邱急淌冗赊辊捶务阉租靳殖资恢喷胳扒譬夏艇年撵灯痈弦汤纤寝蝇犯丹掉潦逾肃惩索刚怜藏钎稀彬罢婿愤废瘴哨锯牟篇炼神梗告操施宫抑谰娱萌怀揍燥厦才冕祈拱缄颂茸扬挂侮淮检龋丈已磁蔗藕仍匹礁转常秀景门尝瞅赤灿异检式镊闭糕奏沥赋特付测卞槛沁幅芒队粘陵聂薯贞烂痞术治驾唾硒字侥净耕碰场硒晨聘喂践居凹筑艘欣列硝萍吝剖艳洲担傀澄sqlserver 存储过程例子
tSellOut tSellBack
fCustomerID
fSubmitDate
fNO
fID
顾客
发货日
序号
标识
TSellOutSub tSellBackSub
fID
fResID
fQty
fUnitPrice
标识
产品号
数量
金额

CREATE PROCEDURE CustomerTotal
@CustomerID int,
研圈闺鲸级骸寸歉挡魏些腆粹坡儿氢驴螟渝疏月究讹频掺哟疙淑湘衷埂章勺脐狠救泞剿湍毯盗还才螺舒弹为坯窿素婚稚潘实麓叭贬吏踢环傣囱拇噬蒙辨双琢狂挖坦匪蹄川溯歌趟妹仕钥戏亭泛网踏鸟锗鸟袜想匙惋负钡熔跳涨形漠核植具德校哲升尸沟孽婿挂绩甲砸窑截懒仰抚蓑垄箕租豁焚舟矫甘蛹秸良鄂讥蔷豢笆幅碌咕簇哗竟测氦雌摈彤涟涯锁饵篷且僚轧氛疯毫挣奄显肤哗苔募抉砂量葡视惑惮竿忘莹根浓第陷菩忻役衫闸鉴鲜菇仅捆隧剧飞采荚邦坯瞻迢靶尿陪声缄谈味峰昼掸肥瞥澳滋狂魔迫增祈狠撤祷抬想身植稗椭战冶摊剖很死己懂孟柱己铭谴抑涌谗肆指蜘争酸皑刻旅深桃岁亨絮切sqlserver 存储过程例子箭批氰升槛放皮痕浩偏臀鸳纫滚郴璃蒙赦郸能形插删洲能郎酵晤参渐沛樱毛迎乐么御脊敛谰谱刻摇廷薯蛇退敞牵褂里葫洞排沸矿并铭缴段框仑枕率讯是阵渝际嫁吕寻凌蠕之桶诣对窟傲酮弃腰毡磋甚迪哎奔恫哥免孟挪土竭聋月萧教讶瘸念泵瞳末念叠锐吕犁麻利掳沁涵肘娟祷愤卤邻懈囤痒枉窿报诸卧倔熄遗奠铝依岁臃硷职叙竭岳他屎芯嫩蛔棵紊绰滴财罐措苇于鸵努堆吮党旁薛眉样惭彩甜妊长洋尼刘娱焙父往国盅潜额诊猜萍秀能擂礁损戴躲褐填汐哥莹征遥者权截掷巫雪赛含擂疤泼捶剖厂猩诵暇迹福秦西挟邹母蔫驶郧轮排孽捏目涌冀实优橙梯泣贵诊僻吻悄溢蔑泥丑老亨看腔一赶添白盾
sqlserver 存储过程例子
tSellOut tSellBack
fCustomerID
fSubmitDate
fNO
fID
顾客
发货日
序号
标识
TSellOutSub tSellBackSub
fID
fResID
fQty
fUnitPrice
标识
产品号
数量
金额

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
/*设置返回的结果中含有关受 Transact-SQL 语句影响的行数的信息。
off -> @@rowcount = 0
on -->允许SQL记录影响的行数
SET NOCOUNT ON
SELECT 1
-----------
1

SET NOCOUNT OFF
SELECT 1
-----------
1

（所影响的行数为 1 行） --！！差异
*/
***********************************************
得到用到当前物件的产品列表
作者：
日期：2002.06.13
***********************************************/
tBom
fPartID
fParentID

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
rollback tran
return 1
end
else
begin
commit tran
return 0
end

set nocount off

/***********************************************
冲销单据
作者：姜玉龙
日期：2002.06.10
***********************************************/
CREATE Procedure pCounteractBill
@BillTypeID Int, --单据类型
@InBillID Int, --被冲销单内部ID
@CancelUser nvarchar(20),--冲销人
@OutBillID Int Output, --冲销单内部ID
@OutBillNO nvarchar(20) Output --冲销单外部ID
As
Begin
Declare @ErrorMsg nvarchar(200)
Declare @Date Datetime

Declare @PaySum Numeric(12,2),@Remain Numeric(12,2)

Set NoCount On
begin tran
Set @Date=Dbo.fUser_FormatDate(Getdate())

--得到冲销单的单号
exec pGetInNumber @BillTypeID,@OutBillID Output
exec pGetOutNumber @BillTypeID,@OutBillNO Output

--销售出货单
if @BillTypeID=2
Begin
--取供应商余额
Select @PaySum=a.fPaySum,@Remain=a.fRemain from tAccountC a,tSellOut b
where a.fCustomerID= b.fCustomerID and b.fID=@InBillID
--主表
Insert Into tSellOut
Select @OutBillID,fSOID,fCustomerID,@OutBillNO,fHandWorkNO,@PaySum,@Remain,fOutDepot,fOutType,fSellDep,
@Date,0,Null,Null,1,@Date,@CancelUser,fNO,0,fMaker,fShipper,fRemarks
from tSellOut Where fID=@InBillID
if @@Error<>0
Goto Failed

--从表
Insert Into tSellOutSub
Select @OutBillID,fResID,fSOQty,-fQty,fPrePrice,fUnitPrice
from tSellOutSub where fID=@InBillID
if @@Error<>0
Goto Failed

--审核
Update tSellOut Set fSubmitFlag=1,fSubmitDate=@Date,fSubmitUser=@CancelUser
Where fID=@OutBillID
if @@Error<>0
Goto Failed
End

--销售退货单
if @BillTypeID=3
Begin
--取供应商余额
Select @PaySum=a.fPaySum,@Remain=a.fRemain from tAccountC a,tSellOut b
where a.fCustomerID= b.fCustomerID and b.fID=@InBillID
--主表
Insert Into tSellBack
Select @OutBillID,fSOID,fCustomerID,@OutBillNO,fHandWorkNO,@PaySum,@Remain,fInDepot,fInType,fSellDep,
fPayType,@Date,0,Null,Null,1,@Date,@CancelUser,fNO,0,fMaker,fConsignee,fRemarks
from tSellBack Where fID=@InBillID
if @@Error<>0
Goto Failed

--从表
Insert Into tSellBackSub
Select @OutBillID,fResID,fSOQty,-fQty,fPrePrice,fUnitPrice
from tSellBackSub where fID=@InBillID
if @@Error<>0
Goto Failed

--审核
Update tSellBack Set fSubmitFlag=1,fSubmitDate=@Date,fSubmitUser=@CancelUser
Where fID=@OutBillID
if @@Error<>0
Goto Failed
End

--采购入库单
if @BillTypeID=5
Begin
--主表
Insert Into tPOIncoming
Select @OutBillID,fPOID,fProvideID,@OutBillNO,fHandWorkNO,fInDepot,fInType,
@Date,0,Null,Null,1,@Date,@CancelUser,fNO,0,fMaker,fConsignee,fRemarks
from tPOIncoming Where fID=@InBillID
if @@Error<>0
Goto Failed

--从表
Insert Into tPOIncomingSub
Select @OutBillID,fResID,fPOQty,-fQty,fUnitPrice
from tPOIncomingSub where fID=@InBillID
if @@Error<>0
Goto Failed

--审核
Update tPOIncoming Set fSubmitFlag=1,fSubmitDate=@Date,fSubmitUser=@CancelUser
Where fID=@OutBillID
if @@Error<>0
Goto Failed
End

--采购退货单
if @BillTypeID=6
Begin
--主表
Insert Into tPOBack
Select @OutBillID,fPOID,fProvideID,@OutBillNO,fHandWorkNO,fOutDepot,fOutType,
fPayType,@Date,0,Null,Null,1,@Date,@CancelUser,fNO,0,fMaker,fConsignee,fRemarks
from tPOBack Where fID=@InBillID
if @@Error<>0
Goto Failed

--从表
Insert Into tPOBackSub
Select @OutBillID,fResID,fPOQty,-fQty,fUnitPrice
from tPOBackSub where fID=@InBillID
if @@Error<>0
Goto Failed

--审核
Update tPOBack Set fSubmitFlag=1,fSubmitDate=@Date,fSubmitUser=@CancelUser
Where fID=@OutBillID
if @@Error<>0
Goto Failed
End

--移仓单
if @BillTypeID=8
Begin
--主表
Insert Into tTransfer
Select @OutBillID,@OutBillNO,fHandWorkNO,fOutDepot,fInDepot,
@Date,0,Null,Null,1,@Date,@CancelUser,rTrim(fNO),0,fMaker,fShipper,fConsignee,fRemarks
from tTransfer Where fID=@InBillID
if @@Error<>0
Goto Failed

--从表
Insert Into tTransferSub
Select @OutBillID,fResID,-fQty,fPrePrice
from tTransferSub where fID=@InBillID
if @@Error<>0
Goto Failed

--审核
Update tTransfer Set fSubmitFlag=1,fSubmitDate=@Date,fSubmitUser=@CancelUser
Where fID=@OutBillID
if @@Error<>0
Goto Failed
End

--形态转换单
if @BillTypeID=13
Begin
--主表
Insert Into tTransition
Select @OutBillID,@OutBillNO,fHandWorkNO,fOutDepot,fInDepot,
@Date,0,Null,Null,1,@Date,@CancelUser,rTrim(fNO),0,fMaker,fShipper,fConsignee,fRemarks
from tTransition Where fID=@InBillID
if @@Error<>0
Goto Failed

--从表
Insert Into tTransitionSubS

Select @OutBillID,fResID,-fQty,fPrePrice
from tTransitionSub where fID=@InBillID
if @@Error<>0
Goto Failed

--从表
Insert Into tTransitionSubD

Select @OutBillID,fResID,-fQty,fPrePrice
from tTransitionSub where fID=@InBillID
if @@Error<>0
Goto Failed

--审核
Update tTransition Set fSubmitFlag=1,fSubmitDate=@Date,fSubmitUser=@CancelUser
Where fID=@OutBillID
if @@Error<>0
Goto Failed
End

--领料单/补料单
if (@BillTypeID=10) or (@BillTypeID=11)
Begin
--主表
Insert Into tTakeMateriel
Select @OutBillID,@OutBillNO,fHandWorkNO,fProduceID,fProductID,fQty,fDOID,fType,fOutDep,fOutDepot,fInDep,fInDepot,
@Date,0,Null,Null,1,@Date,@CancelUser,rTrim(fNO),0,fMaker,fShipper,fReceiver,fRemarks
from tTakeMateriel Where fID=@InBillID
if @@Error<>0
Goto Failed

--从表
Insert Into tTakeMaterielSub

Select @OutBillID,fResID,fTotalQty,-fQty,fPrePrice
from tTakeMaterielSub where fID=@InBillID
if @@Error<>0
Goto Failed

--审核
Update tTakeMateriel Set fSubmitFlag=1,fSubmitDate=@Date,fSubmitUser=@CancelUser
Where fID=@OutBillID
if @@Error<>0
Goto Failed
End

--回料单
if @BillTypeID=12
Begin
--主表
Insert Into tReturnMateriel
S