注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

海宏软件的个人主页

用机器解放人的双手

 
 
 

日志

 
 

sqlServer在存储过程中触发raisError16级错误,事务处理transaction,海宏软件  

2013-06-08 00:58:05|  分类: 默认分类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
--sqlServer在存储过程中触发raisError16级错误,事务处理,海宏软件
if object_id('passMarketReport') is not null drop procedure passMarketReport
go

--用法: 
CREATE Procedure passMarketReport @sKey varchar(50), @lCheckPass bit=1
as --审核市场报表,传入marketreport表的skey,作者:海宏软件,wlb、zch
begin
declare @err int
--市场编号sID、年度nYear、季度nSeason
Declare @s varChar(50), @s2 varChar(2000), @sID varchar(50), @sKeyYear varChar(50), @sql varChar(2000), @sUser varChar(50) 
declare @n int, @nYear int, @nSeason int, @lPass bit, @lPost bit
---------------------------------------------开启事务--------------------------------------------------
Select @err=0 --错误代码,因为sql遇到raiserror后仍然能继续往下执行,要通过@@error错误码判断是否出错,以回滚事务
BEGIN TRAN
-----------------------------------------获取市场ID,报表年份-------------------------------------------
Select @sID=sID, @nYear=nYear, @nSeason=nSeason, @lPass=lPass, @lPost=lPost From MarketReport Where sKey=@sKey 
select @sID=isNull(@sID,''), @lPass=isNull(@lPass,0)
select @err=@err+@@error --错误计数
--检查已审核
if @lCheckPass=1 and @lPass=1 begin select @s='市场报表'+@sID+'已经审核了!' raisError(@s, 16, 1) end
select @err=@err+@@error --错误计数
--检测该ReportsID的市场有没有
if @sID='' begin select @s='找不到市场报表'+@sID+'' raisError(@s, 16, 1) end
select @err=@err+@@error --错误计数
--市场管理员id
select @sUser=sUser from market where sID=@sID
select @err=@err+@@error --错误计数
---------------------------1:处理年表。检查有无年表数据,无则insert,有则update---------------------------
select @sKeyYear=sKey from marketReportYear where sID=@sID and nYear=@nYear 
select @sKeyYear=isNull(@sKeyYear,'')
--没有年表没有数据 执行插入
if @sKeyYear='' Begin
select @sKeyYear=replace(newID(),'-','') --生成新key
insert into marketReportYear (sID, nYear, nSeason, sKey) values(@sID, @nYear, @nSeason, @sKeyYear) --插入空数据行
select @err=@err+@@error --错误计数
    /*Insert into MarketReportYear (sID,nYear,nSeason ,dDate,sUser,lPost ,dPost,sPoster,lPass,
dPass,sPasser,nMoney,nSize,nZhanDiMianJi,nPerson,nShop,nShopSaled,nShopHired,nShopOther,
nTanWeiLiYongLv,nJiaoYiLiang,nProfit,nGuanLiFei,nTanWeiFei,nQiTaLiRun,lTuDiShouXu,sTuDiQuanShu,
sShiChangGuiMo, sJingYingSheShi,lISO9000,lISO4000,lFuWuBiaoZhun,lDianZiJieSuan,
lZiYouWangZhan, lXinXiPingTai, lWuLiuPeiSong, lLengCangSheShi,nLengCangMianJi ,nLengCangChe,
lZhiJianZhongXin,lAnQuanJianKong,lFeWuChuLi,nWangShang,lGongShangZhuCe)
    Select sID,nYear,0 ,dDate,sUser,lPost ,dPost,sPoster,lPass,
dPass,sPasser,nMoney,nSize,nZhanDiMianJi,nPerson,nShop,nShopSaled,nShopHired,nShopOther,
nTanWeiLiYongLv,nJiaoYiLiang,nProfit,nGuanLiFei,nTanWeiFei,nQiTaLiRun,lTuDiShouXu,sTuDiQuanShu,
sShiChangGuiMo, sJingYingSheShi,lISO9000,lISO4000,lFuWuBiaoZhun,lDianZiJieSuan,
lZiYouWangZhan, lXinXiPingTai, lWuLiuPeiSong, lLengCangSheShi,nLengCangMianJi ,nLengCangChe,
lZhiJianZhongXin,lAnQuanJianKong,lFeWuChuLi,nWangShang,lGongShangZhuCe 
From MarketReport Where sID = @sID And nYear = @nYear And  nSeason=@nSeason */
End
---------------更新年表数据---------------
Update Y Set 
--统计的数字
nMoney=isNull(Y.nMoney,0)+isNull(M.nMoney,0), nJiaoYiLiang =isNull(Y.nJiaoYiLiang,0)+isNull(M.nJiaoYiLiang,0), 
nProfit=isNull(Y.nProfit,0)+isNull(M.nProfit,0),  nGuanLiFei=isNull(Y.nGuanLiFei,0)+isNull(M.nGuanLiFei,0), 
nTanWeiFei=isNull(Y.nTanWeiFei,0)+isNull(M.nTanWeiFei,0), nQiTaLiRun=isNull(Y.nQiTaLiRun,0)+isNull(M.nQiTaLiRun,0), 
--覆盖的数据
nSize=M.nSize, nZhanDiMianJi=M.nZhanDiMianJi, nPerson=M.nPerson, 
nShop=M.nShop, nShopSaled=M.nShopSaled, nShopHired=M.nShopHired, nShopOther=M.nShopOther,
nTanWeiLiYongLv=M.nTanWeiLiYongLv, lTuDiShouXu=M.lTuDiShouXu, sTuDiQuanShu=M.sTuDiQuanShu,
sShiChangGuiMo=M.sShiChangGuiMo, sJingYingSheShi=M.sJingYingSheShi, 
lISO9000=M.lISO9000, lISO4000=M.lISO4000, lFuWuBiaoZhun=M.lFuWuBiaoZhun, 
lDianZiJieSuan=M.lDianZiJieSuan, lZiYouWangZhan=M.lZiYouWangZhan, lXinXiPingTai=M.lXinXiPingTai, 
lWuLiuPeiSong=M.lWuLiuPeiSong, lLengCangSheShi=M.lLengCangSheShi, nLengCangMianJi=M.nLengCangMianJi, 
nLengCangChe=M.nLengCangChe, lZhiJianZhongXin=M.lZhiJianZhongXin, lAnQuanJianKong=M.lAnQuanJianKong, 
lFeWuChuLi=M.lFeWuChuLi, nWangShang=M.nWangShang, lGongShangZhuCe=M.lGongShangZhuCe 
From MarketReportYear As Y, 
(select * from marketReport where sKey=@sKey) as M 
Where Y.sKey=@sKeyYear
select @err=@err+@@error --错误计数
------------------------------------------复制一份季度表------------------------------------------
select @sql=''
--读取所有字段,不读有变动的、不允许写入的
declare cur cursor local for select Name from sysColumns where id=object_id('marketReport') 
and name not in ('nID', 'sKey', 'nYear', 'nSeason', 'lPass', 'lPost', 'dDate')
open cur
--生成待写入字段清单
fetch next from cur into @s
while @@fetch_status=0 begin
if @sql<>'' select @sql=@sql+', '
select @sql=@sql+@s --组合生成字段名
fetch next from cur into @s --继续读取
end
close cur  --释放游标资源
deallocate cur
--生成新年度季度、key
select @s=replace(newid(),'-',''), @nSeason=@nSeason+1
if @nSeason>4 Select @nYear=@nYear+1, @nSeason=1
--写入季度表
select @s2='insert into marketReport (sKey, nYear, nSeason, lPass, lPost, dDate, '+@sql+') 
select '''+@s+''', '+convert(varChar(50),@nYear)+', '+convert(varChar(50),@nSeason)+', 0, 0, getDate(), '+@sql+' 
from marketReport where sKey='''+@sKey+''' '
print @s2
exec(@s2) --执行脚本写入
select @err=@err+@@error --错误计数
-----------------------------------更新当前marketReport为已审核-----------------------------------
update marketReport set lPass=1, dPass=getDate(), sPasser=@sUser, 
dPost=(case when @lPost=1 then dPost else getDate() end),  --原来已提交了的不动
sPoster=(case when @lPost=1 then sPoster else @sUser end),  --提交人
lPost=1
where sKey=@sKey
select @err=@err+@@error --错误计数
----------------------------------------------完成-----------------------------------------------
if @err=0 Begin --无错误
COMMIT TRAN --提交事务
return 0
End
Else Begin
ROLLBACK TRAN --错误,事务回滚、撤销
print '错误:'+convert(varChar(50),@err)+',撤销事务'
return @err
End
end 
go


/*
一直认为16级以上的错误,sql过程就停了,试了试,原来没停。
试验结果:即使有raisError('xxx',16,1)这种错误,sql存储过程仍然是继续往下执行的,但是可以用@@error取错误号。
*/
 
  评论这张
 
阅读(718)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017