USE [gdss3.0]
GO
/****** Object: StoredProcedure [dbo].[UNI_STATISTICS_STORE_TREND] Script Date: 12/30/2014 08:36:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 统计报表-存粮总量变化趋势
ALTER PROC [dbo].[UNI_STATISTICS_STORE_TREND]
@QueryType INT,
@MainTitle VARCHAR(64) OUTPUT,
@SubTitle VARCHAR(64) OUTPUT,
@FootTitle VARCHAR(64) OUTPUT,
@YAxisTitle VARCHAR(64) OUTPUT,
@Max INT OUTPUT,
@Min INT OUTPUT
AS
SET @FootTitle=''
SET @YAxisTitle='存粮总量变化趋势 单位(吨)'
DECLARE @Year VARCHAR(4)
DECLARE @Month INT
DECLARE @MonthStr VARCHAR(2)
SET @Year=CAST(YEAR(GETDATE()) AS VARCHAR(4))
SET @MainTitle=@Year+'年存粮总量变化趋势'
IF (@QueryType=0)
SET @SubTitle='[按粮食品种分析]'
ELSE
SET @SubTitle='[按粮食性质分析]'
DECLARE @RESULT TABLE
(
ChartName VARCHAR(20),
ChartColor VARCHAR(20),
ChartLineWidth INT,
ChartValue DECIMAL(10, 2),
ChartProperty INT DEFAULT 0
)
DECLARE @RESULT_tmp TABLE
(
id int identity(1,1),
ChartName VARCHAR(20),
ChartColor VARCHAR(20),
ChartLineWidth INT,
ChartValue DECIMAL(10, 2),
ChartProperty INT DEFAULT 0
)
DECLARE @CurrentStore DECIMAL(19,6) --当前库存
DECLARE @PreStore DECIMAL(19,6) --前一个月库存
DECLARE @MonthInOut DECIMAL(19,6) --当月入库-出库
DECLARE @StartMonth INT
DECLARE @StopMonth INT
DECLARE @TradeMonth VARCHAR(20)
DECLARE @BeginMonth INT
DECLARE @EndMonth INT
SET @Month=0
WHILE (@Month<12)
BEGIN
SET @Month = @Month + 1
IF (@Month<10)
SET @MonthStr='0' + CAST(@Month AS VARCHAR(2))
ELSE
SET @MonthStr=CAST(@Month AS VARCHAR(2))
INSERT INTO @RESULT(ChartName, ChartProperty) VALUES(@MonthStr + '月', 2)
END
DECLARE @Color CHAR(7)
DECLARE @PropertyID INT
DECLARE @PropertyName VARCHAR(128)
IF (@QueryType=0) -- 按粮食品种分析
BEGIN
DECLARE Cur CURSOR LOCAL SCROLL
FOR
SELECT goods_kind_id, goods_kind_name FROM GoodsKind
OPEN Cur
FETCH FIRST FROM Cur INTO @PropertyID, @PropertyName
WHILE(@@fetch_status=0)
BEGIN
EXEC UNI_RANDOM_COLOR @Color OUTPUT
SET @Month=0
WHILE (@Month<12)
BEGIN
SET @Month = @Month + 1
IF (@Month<10)
SET @MonthStr='0' + CAST(@Month AS VARCHAR(2))
ELSE
SET @MonthStr=CAST(@Month AS VARCHAR(2))
INSERT INTO @RESULT(ChartName, ChartColor, ChartLineWidth, ChartProperty) VALUES(@PropertyName,@Color,2,1)
SELECT @CurrentStore=WSI.store_count FROM WarehouseStoreInfo WSI WHERE WSI.store_status = 1 AND WSI.grain_kind = @PropertyID
SET @StartMonth=@BeginMonth-1
SET @StopMonth=@EndMonth
SET @PreStore = @CurrentStore
DELETE FROM @RESULT_tmp
EXEC UNI_RANDOM_COLOR @Color OUTPUT
WHILE(@StopMonth > @StartMonth)
BEGIN
EXEC UNI_DATETIME_CONVER_CHAR @StopMonth, @MonthStr OUTPUT
SET @TradeMonth=SUBSTRING(@MonthStr,1,6)
SELECT @MonthInOut = SUM(billincount-billoutcount)/1000 FROM view_billdates WHERE billstatus=1 AND SUBSTRING(CONVERT(NVARCHAR,billdate,112),1,6)= @Year + @MonthStr AND billkindid=@PropertyID
IF(@TradeMonth = SUBSTRING(CONVERT(NVARCHAR,@StopMonth,112),1,6))
BEGIN
INSERT INTO @RESULT_tmp (ChartValue) VALUES (@PreStore/1000)
END
ELSE
BEGIN
SET @PreStore = @PreStore - ISNULL(@MonthInOut,0)
INSERT INTO @RESULT_tmp (ChartValue) VALUES (@PreStore/1000)
END
SET @StopMonth = @StopMonth - 1
END
INSERT INTO @RESULT SELECT TMP.ChartName,TMP.ChartColor,TMP.ChartLineWidth,TMP.ChartValue,TMP.ChartProperty FROM @RESULT_tmp TMP ORDER BY TMP.id DESC
FETCH NEXT FROM Cur INTO @PropertyID, @PropertyName
END
END
CLOSE Cur
DEALLOCATE Cur
END
ELSE -- 按粮食性质分析
BEGIN
DECLARE Cur CURSOR LOCAL SCROLL
FOR
SELECT grain_attribute_id, grain_attribute_name FROM GrainAttribute
OPEN Cur
FETCH FIRST FROM Cur INTO @PropertyID, @PropertyName
WHILE(@@fetch_status=0)
BEGIN
EXEC UNI_RANDOM_COLOR @Color OUTPUT
INSERT INTO @RESULT(ChartName, ChartColor, ChartLineWidth, ChartProperty) VALUES(@PropertyName,@Color,2,1)
SET @Month=0
WHILE (@Month<12)
BEGIN
SET @Month = @Month + 1
IF (@Month<10)
SET @MonthStr='0' + CAST(@Month AS VARCHAR(2))
ELSE
SET @MonthStr=CAST(@Month AS VARCHAR(2))
INSERT INTO @RESULT (ChartValue)
SELECT SUM(billincount-billoutcount)/1000 FROM view_billdates WHERE billstatus=1 AND SUBSTRING(CONVERT(VARCHAR(6),billdate,112),1,6) = @Year + @MonthStr AND billattributeid=@PropertyID
END
FETCH NEXT FROM Cur INTO @PropertyID, @PropertyName
END
CLOSE Cur
DEALLOCATE Cur
END
UPDATE @RESULT SET ChartValue=0 WHERE ChartValue IS NULL AND ChartProperty=0
SELECT @Max=MAX(ChartValue), @Min=MIN(ChartValue) FROM @RESULT WHERE ChartName IS NULL AND ChartValue <> 0
IF (@Max IS NULL AND @Min IS NULL)
BEGIN
SET @Max=100
SET @Min=0
END
ELSE IF (@Max IS NULL)
SET @Max = @Min + 100
ELSE IF (@Min IS NULL)
SET @Min = @Max - 100
IF (@Max = @Min)
SET @Min=0
SELECT * FROM @RESULT
RETURN
以上是存储过程，按粮食品种查询查不出数据，求教各位眼尖的大神指导！
------解决思路----------------------
穿一批参数进来，逐个逐个语句的调试，找到问题出在那一句SQL的哪个条件上，只能如此，别无他法，别怕麻烦。。。
------解决思路----------------------
在所有会返回值的地方设置变量，print或者select 出来，看看是否按照预期逻辑运行
------解决思路----------------------
直接参数赋值， 语句直接运行，一步步调试吧