sqlserver-存储过程例子VIP免费

下载后可任意编辑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<=@EndDateInsert Into @CustomerStockIOSelect a.fSubmitDate,'退货',a.fNO,1,b.fResID,-b.fQty,b.fUnitPricefrom tSellBack a,tSellBackSub bwhere a.fID=b.fID and a.fCustomerID=@CustomerID and a.fSubmitDate>=@BeginDate and a.fSubmitDate<=@EndDateSelect fProductID,sum(fQty)as fQty,sum(fUnitPrice)as fUnitPrice, sum(fQty*fUnitPrice)as fSum 下载后可任意编辑from @CustomerStockIOgroup by fProductIDorder by fProductIDSet NoCount OFFEND/*设置返回的结果中含有关受 Transact-SQL 语句影响的行数的信息。 off -> @@rowcount = 0 on -->允许 SQL 记录影响的行数 SET NOCOUNT ON SELECT 1 ----------- 1 SET NOCOUNT OFF SELECT 1 ----------- 1 （所影响的行数为 1 行） --！！差异*/***********************************************得到用到当前物件的产品列表作者：日期：2024.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 @LevelCou...