sqlserver函数存储过程

《sqlserver函数存储过程》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、---关联更新的语句updateShopBoughtNotesetProductAmount=(selectsum(productNum)fromShopBoughtNoteProductListbwhereb.bnid=ShopBoughtNote.bnid)whereexists(select1fromShopBoughtNoteProductListbwhereb.bnid=ShopBoughtNote.bnid)declare@iintset@i=10declare@sqlusevarchar(100),@sql1varchar(1000),@sql2va

2、rchar(1000),@sql3varchar(1000)while@i<16beginset@sqluse='use[shop'+cast(@iasvarchar)+'_3s360]'EXEC(@sqluse)if(notexists(select*fromShopKuQu))beginBeginTransactionset@sql1='use[shop'+cast(@iasvarchar)+'_3s360]insertintoShopKuQu(QYName,EAID)values(区域1,'+@i+')'EXEC(@sql1)endif(notexists(

3、select*fromShopCangKuHuoJia))beginset@sql2='use[shop'+cast(@iasvarchar)+'_3s360]insertintoShopCangKuHuoJia(HJName,QYID)values(货架1,1)'EXEC(@sql2)endif(notexists(select*fromShopStoragePlace))beginset@sql3='use[shop'+cast(@iasvarchar)+'_3s360]insertintoShopStoragePlace(HJID,PlaceName,Mia

4、nJi,SYMianJI)values(1,位置1,100,100)'EXEC(@sql3)endset@i=@i+1CommitTransactionendIf(@@ERROR<>0)RollbackTransactionUPDATEShopBoughtNoteSETProductAmount=DBO.GetProductListCount(BNID),ProductMoney=dbo.GetProductListMoney(BNID)SELECT*FROMShopBoughtNoteProductListWHEREBNID=1001--获取采购单商品数量cre

5、atefunctionGetProductListCount(@bnidint)returnsintbegindeclare@numintSELECT@num=SUM(ProductNum)FROMShopBoughtNoteProductListWHEREBNID=@bnidif(@numisnull)set@num=0return@numend--获取采购单商品数量createfunctionGetProductListMoney(@bnidint)returnsMONEYbegindeclare@MONEYMONEYSELECT@MONEY=SUM(Pric

6、ing)FROMShopBoughtNoteProductListWHEREBNID=@bnidif(@MONEYisnull)set@MONEY=0return@MONEYend=============创建存储过程=============Procedure[dbo].[UP_getCrumb](@IDint)asDECLARE@cnameVARCHAR(600)SET@cname=''while@ID>0begindeclare@cname2varchar(600)set@cname2=(selectCategoryNamefromProductCatego

7、rywhereCategoryID=@ID)IF(@cname<>'')set@cname=''+@cname2+'>'+@cnameELSEset@cname=''+@cname2+'>'+@cnameset@ID=(selectParentIDfromProductCategorywhereCategor

8、yID=@