SQL存储过程【笔记】一个较简单的库存月结

关于库存月结的一个存储过程：

 

1、测试版本)

select @@version

/*********************************
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
	Oct 14 2005 00:33:37 
	Copyright (c) 1988-2005 Microsoft Corporation
	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
*********************************/

2、相关表)

SM_Company   --公司信息表
BS_Product   --产品信息表
IM_Stock   --库存表
IM_In    --入库主表
IM_Out    --出库主表
StockStorage  --月结主表
StockStorageDetail --月结明细表 

3、存储过程脚本)

--库存月结存储过程
/**
Q1：该公司未曾做月结，与出入库最早日期比较，提醒需要从某一月份开始做月结；
Q2：本次月结之前某几个月份未曾做月结，提醒要做完整月结；
Q3：正确月结的计算。
**/

alter procedure LS_Get_IMStockMonth
@companyid int,				--月结公司id
@date datetime,				--月结月份
@staffid int,				--月结人id
@warehouseid int,			--月结仓库id
@createtime datetime,		--月结创建日期
@remark text,				--月结备注
@flag int,					--操作：0 月结，1 删除
@storid int,				--月结主表id
@text varchar(1000) output	--月结提示
as
begin
set transaction isolation level read uncommitted
set nocount on
begin transaction
declare @mindatein datetime
declare @mindateout datetime
declare @monthdate datetime
declare @storageId int
declare @err int
declare @month int
declare @maxdate datetime

select @mindatein = min(a.createtime)
from IM_In a join IM_Warehouse b on a.warehouseid = b.id
where b.companyid = @companyid and b.id = @warehouseid
select @mindateout = min(a.createtime)
from IM_Out a join IM_Warehouse b on a.warehouseid = b.id
where b.companyid = @companyid and b.id = @warehouseid
--得到发生库存变化的初始时间
select @monthdate = (case when @mindatein<@mindateout then @mindatein else @mindateout end)

--判断是否删除月结数据
if @flag=1
goto nextdelete
else
begin
select @maxdate = max(paperDate) from StockStorage where companyid=@companyid and warehouseid=@warehouseid
if exists (select 1 from StockStorage 
		where companyid=@companyid and warehouseid=@warehouseid 
			and convert(varchar(6),paperDate,112)=convert(varchar(6),@date,112))
begin
set @text = '1,公司'+replace(convert(varchar(7),@date,120),'-','年')+'月月结数据已新增！'
commit tran
return
end
--判断月结月份前是否存在断月份月结的情况
/*找出从有出入库其实日期到做本次月结日期的所有月份*/
select @maxdate = isnull(@maxdate,@monthdate)
select @month = datediff(mm,@maxdate,@date)
--判断在这些月份中是否存在断月份月结的情况
if @month < 0 or @month > 1
begin
set @text = '1,公司'+replace(convert(varchar(7),@date,120),'-','年')+'月之前未做月结数据或需从'
					+replace(convert(varchar(7),@monthdate,120),'-','年')+'月做月结！'
commit tran
return
end
else
goto nextinsert	--跳转至月结主表及明细表插入节点
end

nextinsert:
--插入月结主表对应数据
insert into StockStorage
select @companyid,@date,@staffid,@warehouseid,@createtime,@remark
--获取插入到月结主表最新的主键id
select @storageId = scope_identity()

/*计算库存月结数据逻辑SQL语句，可以利用临时表或with cte的用法，下文以cte为主。*/

--向月结子表插入数据
insert into StockStorageDetail
select @storageId,productid,quantity
from cte
if(@@error<>0) goto Failure
commit transaction
set @text = '0,公司'+replace(convert(varchar(7),@date,120),'-','年')+'月月结数据插入已完成！'
return

nextdelete:
select @companyid = companyid from StockStorage where id = @storid
select @warehouseid = warehouseid from StockStorage where id = @storid
select @date = paperDate from StockStorage where id = @storid
--删除子表数据，删除对应月份及之后做的所有月结数据
delete b
from StockStorage a join StockStorageDetail b on a.id = b.storageId
where a.companyid = @companyid and a.warehouseid = @warehouseid
		and convert(varchar(6),a.paperDate,112) >= convert(varchar(6),@date,112)
select @err = abs(@@error)
--删除主表数据，删除对应月份及之后做的所有月结数据
delete from StockStorage
where companyid = @companyid and warehouseid = @warehouseid
		and convert(varchar(6),paperDate,112) >= convert(varchar(6),@date,112)
select @err = @err + abs(@@error)
if(@err<>0) goto Failure
commit transaction
set @text = '0,公司'+replace(convert(varchar(7),@date,120),'-','年')+'月之后月份月结数据删除已完成！'
return

Failure:
rollback transaction
set @text = '1,公司'+replace(convert(varchar(7),@date,120),'-','年')+'月月结数据出现异常！'
commit tran
return

set nocount off

end
go

4、测试)

--做任意月份月结，提示月结初始月份
declare @str varchar(1000)
exec LS_Get_IMStockMonth 15,'2011-11-30',238,47,'2012-02-20',null,0,null,@str output
select @str

/*****************************
1,公司2011年11月之前未做月结数据或需从2011年08月做月结！
******************************/

--做2011年10月份月结数据
declare @str varchar(1000)
exec LS_Get_IMStockMonth 15,'2011-10-30',238,47,'2012-02-20',null,0,null,@str output
select @str

/*****************************
1,公司2011年10月之前未做月结数据或需从2011年08月做月结！
******************************/

--做2011年08月份月结数据
declare @str varchar(1000)
exec LS_Get_IMStockMonth 15,'2011-08-30',238,47,'2012-02-20',null,0,null,@str output
select @str

/*****************************
0,公司2011年08月月结数据插入已完成！
******************************/

--做2011年10月份月结数据
declare @str varchar(1000)
exec LS_Get_IMStockMonth 15,'2011-10-30',238,47,'2012-02-20',null,0,null,@str output
select @str

/*****************************
1,公司2011年10月之前未做月结数据或需从2011年08月做月结！
******************************/

--做2011年09月份月结数据
declare @str varchar(1000)
exec LS_Get_IMStockMonth 15,'2011-09-30',238,47,'2012-02-20',null,0,null,@str output
select @str

/*****************************
0,公司2011年09月月结数据插入已完成！
******************************/

--删除2011年08月份月结数据，会删除之后所有月份月结
select * from StockStorage where companyid = 15
/*****************************
id          companyId   paperDate               
----------- ----------- ----------------------- 
24          15          2011-08-30 00:00:00.000 
25          15          2011-09-30 00:00:00.000 

(2 行受影响)
*****************************/
declare @str varchar(1000)
exec LS_Get_IMStockMonth 15,'2011-08-30',238,47,'2012-02-20',null,1,24,@str output
select @str

/*****************************
0,公司2011年08月之后月份月结数据删除已完成！
******************************/

表结构就不用贴了，看下表名就知道里面有什么了，在这里做个标记，以防后续改进之用！