sqlserver 存储过程例子.docVIP

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 @LevelCount20 Goto Out Insert Into @Tmp Select * from @TmpA Delete From @TmpB Inse