sp_saleslistreport.sql

(Java+SQL)-大型企业JAVA的ERP系统

CREATE PROCEDURE sp_SalesListReport -- 个人销售明细表
        @loginName varchar(30) 
	,@ip varchar(20) 
	,@machineName varchar(30) 
	,@deptId varchar(10)      -- 部门编码 	
	,@employeeId varchar(10)  -- 人员编码 		
	,@beginDate datetime      -- 开始时间
	,@endDate datetime        -- 结束时间

AS DECLARE 
	 @error_var int 
	,@rowcount_var int
        ,@deptName varchar(30)     -- 部门名称
        ,@empName varchar(10)	   -- 姓名
        ,@productId varchar(10)    -- 产品编码
	,@productName  varchar(30) -- 产品名称
        ,@totalPrice float         -- 销售金额
	,@countNum int             -- 记录号
                                
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT 
	IF @error_var <> 0 RETURN -102 
	IF @rowcount_var = 0 RETURN -103 


SELECT @totalPrice = 0;

-- 创建临时表
CREATE TABLE #Tmp_SalesListReport 
(        deptName varchar(30)      -- 部门名称
        ,empName varchar(10)	   -- 姓名
	,productName  varchar(30)  -- 产品名称
        ,totalPrice float          -- 销售金额
        ,beginDate datetime        -- 开始日期
        ,endDate datetime          -- 结束日期        
        ,countNum int
)   

DECLARE product_cur CURSOR FOR 
SELECT productId FROM Production 
   
OPEN product_cur

FETCH NEXT FROM product_cur
INTO @productId


WHILE @@FETCH_STATUS = 0
BEGIN
		  -- 部门名称
		    SELECT @deptName = deptName
		      FROM Department
		     WHERE deptId = @deptId  
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 

		  -- 姓名
		    SELECT @empName = empName
		    FROM Employee
		    WHERE employeeId = @employeeId  
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 
		    
                  -- 产品名称
		    SELECT @productName = productName
		      FROM Production
		     WHERE productId = @productId	
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   

                  -- 销售金额
		    SELECT @totalPrice = sum(totalPrice)
		      FROM SalesSlip,SlipProduct
		     WHERE SalesSlip.slipDate >= @beginDate
		       AND SalesSlip.slipDate < @endDate
		       AND SalesSlip.proposer = @employeeId
		       AND SalesSlip.slipId = SlipProduct.slipId
		       AND SlipProduct.productId = @productId		       
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    
	            
	            INSERT INTO #Tmp_SalesListReport(deptName,empName,productName,totalPrice,beginDate,endDate)
	                                   VALUES(@deptName,@empName,@productName,@totalPrice,@beginDate,@endDate)

	FETCH NEXT FROM product_cur
        INTO @productId
END

CLOSE product_cur

DEALLOCATE product_cur

SELECT @countNum  = count(*)
FROM #Tmp_SalesListReport

UPDATE #Tmp_SalesListReport
   SET countNum = @countNum

SELECT deptName,empName,productName,totalPrice,beginDate,endDate,countNum
  FROM #Tmp_SalesListReport
RETURN 0


















GO