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月之後月份月結資料刪除已完成！
******************************/

表結構就不用貼了，看下錶名就知道里面有什麼了，在這裡做個標記，以防後續改進之用！