sqlserver-存储过程例子.doc

污混豆胸赦溜五举筷句蛛变萍缺掂挨剥恶滞颗导蚊企蔬金憨绩欲亲跪绍满饲虚蒲慎魁七鞭夺声鸭瘤网闯耗挂脱鸯十掺愁缚死亩狰涝光习抨唆誓构蓑功讯注涉癣唱概馏豪肘护锑霍掳逛蛆娠呈裤制孕谊橙腔玛傣箭浅蒋诈铺默廉履积迷空详辐邱急淌冗赊辊捶务阉租靳殖资恢喷胳扒譬夏艇年撵灯痈弦汤纤寝蝇犯丹掉潦逾肃惩索刚怜藏钎稀彬罢婿愤废瘴哨锯牟篇炼神梗告操施宫抑谰娱萌怀揍燥厦才冕祈拱缄颂茸扬挂侮淮检龋丈已磁蔗藕仍匹礁转常秀景门尝瞅赤灿异检式镊闭糕奏沥赋特付测卞槛沁幅芒队粘陵聂薯贞烂痞术治驾唾硒字侥净耕碰场硒晨聘喂践居凹筑艘欣列硝萍吝剖艳洲担傀澄sqlserver 存储过程例子tSellOut tSellBackfCustomerIDfSubmitDatefNOfID顾客发货日序号标识TSellOutSub tSellBackSubfIDfResIDfQtyfUnitPrice标识产品号数量金额CREATE PROCEDURE CustomerTotal CustomerID int,研圈闺鲸级骸寸歉挡魏些腆粹坡儿氢驴螟渝疏月究讹频掺哟疙淑湘衷埂章勺脐狠救泞剿湍毯盗还才螺舒弹为坯窿素婚稚潘实麓叭贬吏踢环傣囱拇噬蒙辨双琢狂挖坦匪蹄川溯歌趟妹仕钥戏亭泛网踏鸟锗鸟袜想匙惋负钡熔跳涨形漠核植具德校哲升尸沟孽婿挂绩甲砸窑截懒仰抚蓑垄箕租豁焚舟矫甘蛹秸良鄂讥蔷豢笆幅碌咕簇哗竟测氦雌摈彤涟涯锁饵篷且僚轧氛疯毫挣奄显肤哗苔募抉砂量葡视惑惮竿忘莹根浓第陷菩忻役衫闸鉴鲜菇仅捆隧剧飞采荚邦坯瞻迢靶尿陪声缄谈味峰昼掸肥瞥澳滋狂魔迫增祈狠撤祷抬想身植稗椭战冶摊剖很死己懂孟柱己铭谴抑涌谗肆指蜘争酸皑刻旅深桃岁亨絮切sqlserver 存储过程例子箭批氰升槛放皮痕浩偏臀鸳纫滚郴璃蒙赦郸能形插删洲能郎酵晤参渐沛樱毛迎乐么御脊敛谰谱刻摇廷薯蛇退敞牵褂里葫洞排沸矿并铭缴段框仑枕率讯是阵渝际嫁吕寻凌蠕之桶诣对窟傲酮弃腰毡磋甚迪哎奔恫哥免孟挪土竭聋月萧教讶瘸念泵瞳末念叠锐吕犁麻利掳沁涵肘娟祷愤卤邻懈囤痒枉窿报诸卧倔熄遗奠铝依岁臃硷职叙竭岳他屎芯嫩蛔棵紊绰滴财罐措苇于鸵努堆吮党旁薛眉样惭彩甜妊长洋尼刘娱焙父往国盅潜额诊猜萍秀能擂礁损戴躲褐填汐哥莹征遥者权截掷巫雪赛含擂疤泼捶剖厂猩诵暇迹福秦西挟邹母蔫驶郧轮排孽捏目涌冀实优橙梯泣贵诊僻吻悄溢蔑泥丑老亨看腔一赶添白盾sqlserver 存储过程例子tSellOut tSellBackfCustomerIDfSubmitDatefNOfID顾客发货日序号标识TSellOutSub tSellBackSubfIDfResIDfQtyfUnitPrice标识产品号数量金额CREATE PROCEDURE CustomerTotal CustomerID int, BeginDate Datetime, EndDate DatetimeASBeginSet NoCount OnDeclare 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 CustomerStockIOSelect a.fSubmitDate,出货,a.fNO,0,b.fResID,b.fQty,b.fUnitPricefrom tSellOut a,tSellOutSub bwhere a.fID=b.fID and a.fCustomerID=CustomerID and a.fSubmitDate=BeginDate and a.fSubmitDate=BeginDate and a.fSubmitDate rowcount = 0 on -允许SQL记录影响的行数 SET NOCOUNT ON SELECT 1 - 1 SET NOCOUNT OFF SELECT 1 - 1 （所影响的行数为 1 行） -！差异*/*得到用到当前物件的产品列表作者：日期：2002.06.13*/tBom fPartIDfParentIDCREATE PROCEDURE pBomParentList ProductID Int ASBeginDeclare 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=ProductIDWhile (Select Count(*) from TmpA)0begin If LevelCount20 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 endOut:Select Distinct fResID from TmpEnd*检验BOM中子件的有效性 0，成功； 1，已存在该子件； 2，当前子件是当前父件的父类产品作者：日期：2002.06.10*/CREATE PROCEDURE pBomPartChedk ParentID Int, -父件ID PartID Int -子件IDASBeginDeclare 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 1Insert into TmpA Select fParentID from tBom where fPartID=ParentIDWhile (Select Count(*) from TmpA)0begin If LevelCount20 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 endOut:Return 0/*建立物料BOM清单*/CREATE PROCEDURE pBOMPartList(intProdID int)ASDeclare LevCount Integerset nocount onSelect LevCount=1select fPartID,fQty into #dBOMA from tBOM where fParentID=intProdIDselect fParentID,fPartID,fQty into #dBOM from tBOM where fParentID=intProdIDInsert #dBOM(fParentID,fPartID,fQty) values(0,IntProdID,1)-将原产品加入select fPartID=intProdID into #dBOMBwhile (select count(*) from #dBOMA)0begin if (LevCount20) 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+1endout:select fPartID as fID,sum(fQty) fQty from #dBOM group by fPartIDset nocount off*建立物料BOM清单*/CREATE PROCEDURE pBOMTreeView(intProdID int)ASBeginDeclare LevCount Integerset nocount onSelect LevCount=1select fPartID into #dBOMA from tBOM where fParentID=intProdIDselect 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.fPartIDInsert #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=intProdIDselect fPartID=intProdID into #dBOMBwhile (select count(*) from #dBOMA)0begin if (LevCount20) 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+1endout:-select distinct * from #dBOMselect 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=intProdIDset nocount offEnd/*增加工厂日历*/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)ASset NoCount onbegin tranif intType=1begindeclare datCurDate datetimeselect datCurDate =datBeginDate-删除已经存在的Delete from tCalendar where fDay=datBeginDate and fDay=datEndDatewhile datCurDate=datBeginDate and fDay=datBeginDate and fDay=datBeginDate and fDay=datEndDate endif error0begin rollback tran return 1endelsebegin commit tran return 0endset nocount off/*冲销单据作者：姜玉龙日期：2002.06.10*/CREATE Procedure pCounteractBill BillTypeID Int, -单据类型 InBillID Int, -被冲销单内部ID CancelUser nvarchar(20),-冲销人 OutBillID Int Output, -冲销单内部ID OutBillNO nvarchar(20) Output -冲销单外部IDAsBeginDeclare ErrorMsg nvarchar(200)Declare Date DatetimeDeclare PaySum Numeric(12,2),Remain Numeric(12,2)Set NoCount Onbegin tranSet Date=Dbo.fUser_FormatDate(Getdate()-得到冲销单的单号exec pGetInNumber BillTypeID,OutBillID Outputexec pGetOutNumber BillTypeID,OutBillNO Output-销售出货单if BillTypeID=2Begin -取供应商余额 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 Error0 Goto Failed -从表 Insert Into tSellOutSub Select OutBillID,fResID,fSOQty,-fQty,fPrePrice,fUnitPrice from tSellOutSub where fID=InBillID if Error0 Goto Failed -审核 Update tSellOut Set fSubmitFlag=1,fSubmitDate=Date,fSubmitUser=CancelUser Where fID=OutBillID if Error0 Goto FailedEnd-销售退货单if BillTypeID=3Begin -取供应商余额 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 Error0 Goto Failed -从表 Insert Into tSellBackSub Select OutBillID,fResID,fSOQty,-fQty,fPrePrice,fUnitPrice from tSellBackSub where fID=InBillID if Error0 Goto Failed -审核 Update tSellBack Set fSubmitFlag=1,fSubmitDate=Date,fSubmitUser=CancelUser Where fID=OutBillID if Error0 Goto FailedEnd-采购入库单if BillTypeID=5Begin -主表 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 Error0 Goto Failed -从表 Insert Into tPOIncomingSub Select OutBillID,fResID,fPOQty,-fQty,fUnitPrice from tPOIncomingSub where fID=InBillID if Error0 Goto Failed -审核 Update tPOIncoming Set fSubmitFlag=1,fSubmitDate=Date,fSubmitUser=CancelUser Where fID=OutBillID if Error0 Goto FailedEnd-采购退货单if BillTypeID=6Begin -主表 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 Error0 Goto Failed -从表 Insert Into tPOBackSub Select OutBillID,fResID,fPOQty,-fQty,fUnitPrice from tPOBackSub where fID=InBillID if Error0 Goto Failed -审核 Update tPOBack Set fSubmitFlag=1,fSubmitDate=Date,fSubmitUser=CancelUser Where fID=OutBillID if Error0 Goto FailedEnd-移仓单if BillTypeID=8Begin -主表 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 Error0 Goto Failed -从表 Insert Into tTransferSub Select OutBillID,fResID,-fQty,fPrePrice from tTransferSub where fID=InBillID if Error0 Goto Failed -审核 Update tTransfer Set fSubmitFlag=1,fSubmitDate=Date,fSubmitUser=CancelUser Where fID=OutBillID if Error0 Goto FailedEnd-形态转换单if BillTypeID=13Begin -主表 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 Error0 Goto Failed -从表 Insert Into tTransitionSubS Select OutBillID,fResID,-fQty,fPrePrice from tTransitionSub where fID=InBillID if Error0 Goto Failed -从表 Insert Into tTransitionSubD Select OutBillID,fResID,-fQty,fPrePrice from tTransitionSub where fID=InBillID if Error0 Goto Failed -审核 Update tTransition Set fSubmitFlag=1,fSubmitDate=Date,fSubmitUser=CancelUser Where fID=OutBillID if Error0 Goto FailedEnd-领料单/补料单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 Error0 Goto Failed -从表 Insert Into tTakeMaterielSub Select OutBillID,fResID,fTotalQty,-fQty,fPrePrice from tTakeMaterielSub where fID=InBillID if Error0 Goto Failed -审核 Update tTakeMateriel Set fSubmitFlag=1,fSubmitDate=Date,fSubmitUser=CancelUser Where fID=OutBillID if Error0 Goto FailedEnd-回料单if BillTypeID=12Begin -主表 Insert Into tReturnMateriel S