資源描述：
《sqlserver函數(shù)存儲(chǔ)過程》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫(kù)。
1、---關(guān)聯(lián)更新的語句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(區(qū)域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--獲取采購(gòu)單商品數(shù)量cre
5、atefunctionGetProductListCount(@bnidint)returnsintbegindeclare@numintSELECT@num=SUM(ProductNum)FROMShopBoughtNoteProductListWHEREBNID=@bnidif(@numisnull)set@num=0return@numend--獲取采購(gòu)單商品數(shù)量createfunctionGetProductListMoney(@bnidint)returnsMONEYbegindeclare@MONEYMONEYSELECT@MONEY=SUM(Pric
6、ing)FROMShopBoughtNoteProductListWHEREBNID=@bnidif(@MONEYisnull)set@MONEY=0return@MONEYend=============創(chuàng)建存儲(chǔ)過程=============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=@
