张老师的一个存储过程

CREATE PROCEDURE [DBO].[REP_STOCK_REPORT]
@ITEM_NUM_START VARCHAR(30),@ITEM_NUM_END VARCHAR(30),@ADDRESS_ID VARCHAR(30),@AREA_ID VARCHAR(30),@LOC_ID VARCHAR(30),@SYS_ID VARCHAR(30),@Check_date varchar(12),@Check_flig varchar(10),@sum_type varchar(10)
AS
DECLARE @BAK_TIME Datetime  ----备份时间

--SET @ITEM_NUM='%'+@ITEM_NUM+'%'
SET @ADDRESS_ID='%'+@ADDRESS_ID+'%'
SET @AREA_ID='%'+@AREA_ID+'%'
SET @LOC_ID='%'+@LOC_ID+'%'
SET @SYS_ID='%'+@SYS_ID+'%'

IF @ITEM_NUM_START=''
BEGIN
  SET @ITEM_NUM_START='0'
END

IF @ITEM_NUM_END=''
BEGIN
  SET @ITEM_NUM_END='Z'
END
--RAISERROR('%s',16,1,@sum_type)
select @BAK_TIME=max(bak_time) from STOCK_DETAIL_HIS where memo='每天自动备份' and conver

t(varchar(10),bak_time,120)<=@Check_date

IF @Check_flig='NowStock' --当前库存查询　His 为历史库存查询
BEGIN
SELECT A.ITEM_NUM,A.ADDRESS_ID,A.QTY,C.LOC_ID,D.SYSTEM_ID,Getdate() as StopTime into #tmp FROM STOCK_DETAIL A
LEFT JOIN LOCATION_ADDRESS B ON B.ADDRESS_ID=A.ADDRESS_ID
LEFT JOIN LOCATION_AREA C ON C.AREA_ID=B.AREA_ID
LEFT JOIN LOCATION D ON D.LOC_ID =C.LOC_ID
WHERE (A.ITEM_NUM Between @ITEM_NUM_START AND @ITEM_NUM_END)  AND A.ADDRESS_ID LIKE @ADDRESS_ID  AND B.AREA_ID LIKE @AREA_ID
AND D.LOC_ID LIKE @LOC_ID AND ISNULL(D.SYSTEM_ID,'') LIKE @SYS_ID
ORDER BY A.ITEM_NUM,A.ADDRESS_ID

IF @sum_type='明细'
BEGIN
SELECT ITEM_NUM,ADDRESS_ID,QTY,LOC_ID,SYSTEM_ID,

StopTime FROM #tmp where qty<>0
END
ELSE
BEGIN
SELECT ITEM_NUM,'汇总' AS ADDRESS_ID,SUM(QTY) AS QTY,LOC_ID,SYSTEM_ID,StopTime  FROM #tmp where qty<>0  GROUP BY ITEM_NUM,LOC_ID,SYSTEM_ID,StopTime
END
END