CREATE PROC [dbo].[P_CKGL_detailsign]
(
@AccountingPeriod_start VARCHAR(10),--起始会计期
@AccountingPeriod_end VARCHAR(10),--结束会计期
@ProductNumber VARCHAR(10),--商品编码
@Provisional VARCHAR(60)--临时表名称
)
ASSET NOCOUNT ON
DECLARE @AccountingPeriod VARCHAR(10)--会计期
DECLARE @sql_str VARCHAR(2000)--SQL语句字符串变量
DECLARE @dy VARCHAR(1)--单引号
set @dy=''''
DECLARE AccountingPeriod_cur CURSOR--会计期指针
FOR select FinanceName from SYS_KJQ where FinanceName between @AccountingPeriod_start and @AccountingPeriod_end
set @sql_str='delete '+@Provisional
exec(@sql_str)
--print @sql_str
BEGIN
OPEN AccountingPeriod_cur
FETCH NEXT FROM AccountingPeriod_cur INTO @AccountingPeriod
--期初
set @sql_str='insert '+@Provisional+'(NO,Abstract,AccountingPeriod,ProductNumber,Date,LeftAmount,LeftMoney) select '+@dy+'1'+@dy+','+@dy+'期初'+@dy+','+@dy+@AccountingPeriod_start+@dy+','+@dy+@ProductNumber+@dy+','+'[date],[Opening Number],[Opening Money]'+' from Storage_AccPeriod'+' where isnull([Accounting Period],'+@dy+@dy+') = '+@dy+@AccountingPeriod_start+@dy+' and isnull(ProductNumber,'+@dy+@dy+')='+@dy+@ProductNumber+@dy
print @sql_str
exec(@sql_str)
WHILE @@FETCH_STATUS=0 --循环
BEGIN
EXEC P_CKGL_detailsign_product @AccountingPeriod,@ProductNumber,@Provisional
FETCH NEXT FROM AccountingPeriod_cur INTO @AccountingPeriod
END--循环
CLOSE AccountingPeriod_cur
DEALLOCATE AccountingPeriod_cur
END
SET NOCOUNT OFF=======================
如上代码是什么意思?
(
@AccountingPeriod_start VARCHAR(10),--起始会计期
@AccountingPeriod_end VARCHAR(10),--结束会计期
@ProductNumber VARCHAR(10),--商品编码
@Provisional VARCHAR(60)--临时表名称
)
AS SET NOCOUNT ON DECLARE @AccountingPeriod VARCHAR(10)--会计期
DECLARE @sql_str VARCHAR(2000)--SQL语句字符串变量
DECLARE @dy VARCHAR(1)--单引号
set @dy=''''
DECLARE AccountingPeriod_cur CURSOR--会计期指针
FOR
select FinanceName
from SYS_KJQ
where FinanceName between @AccountingPeriod_start and @AccountingPeriod_end set @sql_str='delete '+@Provisional
exec(@sql_str) --print @sql_str
BEGIN
OPEN AccountingPeriod_cur
FETCH NEXT FROM AccountingPeriod_cur INTO @AccountingPeriod
--期初
set @sql_str='insert '+@Provisional
+'(NO,Abstract,AccountingPeriod,ProductNumber,Date,LeftAmount,LeftMoney) select '
+@dy+'1'+@dy+','+@dy+'期初'+@dy+','+@dy+@AccountingPeriod_start+@dy
+','+@dy+@ProductNumber+@dy+','+'[date],[Opening Number],[Opening Money]'
+' from Storage_AccPeriod'+' where isnull([Accounting Period],'+@dy+@dy+') = '
+@dy+@AccountingPeriod_start+@dy+' and isnull(ProductNumber,'+@dy+@dy+')='
+@dy+@ProductNumber+@dy
print @sql_str
exec(@sql_str)
WHILE @@FETCH_STATUS=0 --循环
BEGIN
EXEC P_CKGL_detailsign_product @AccountingPeriod,@ProductNumber,@Provisional
FETCH NEXT FROM AccountingPeriod_cur INTO @AccountingPeriod
END--循环
CLOSE AccountingPeriod_cur
DEALLOCATE AccountingPeriod_cur
END SET NOCOUNT OFF