调用ADO存储过程(SQL SERVER2000)第一次没问题,第二次就出错,晕!!!
调用过程:
//执行存储过程
try
{
//关闭存储过程对象
pSmsMT->m_pADOSP->Close();
pSmsMT->m_pADOSP->ProcedureName = "cp_AfterSend";
pSmsMT->m_pADOSP->Parameters->Refresh();
pSmsMT->m_pADOSP->Parameters->ParamByName("@IdMap")->DataType = ftString;
pSmsMT->m_pADOSP->Parameters->ParamByName("@IdMap")->Value = strRSMap;
pSmsMT->m_pADOSP->Parameters->ParamByName("@MaxSendId")->DataType = ftLargeint;
pSmsMT->m_pADOSP->Parameters->ParamByName("@MaxSendId")->Value = 0;
pSmsMT->m_pADOSP->Prepared = true;
pSmsMT->m_pADOSP->ExecProc();
}
catch(...)
{
//记录错误日志
pSmsMT->m_pLogFile->AddLine(CLogFile::EP_FATAL_ERROR,"执行存储过程cp_AfetrSend失败!");
}
存储过程:
ALTER proc cp_AfterSend
@IdMap varchar(256),
@MaxSendId bigint OUTPUT
/*
返回:
如果成功返回 0,失败返回负数
*/
AS
DECLARE
@strIdMap varchar(256),
@Rid bigint,
@nPkg smallint,
@nBegin bigint,
@nPos int,
@nPos1 int,
@nPos2 int,
@i int,
@nRet int
set @nRet = 0
set @strIdMap = @IdMap
set @MaxSendId = -1
WHILE(LEN(@strIdMap) > 0)
BEGIN
/*先分解参数*/
set @nPos = CHARINDEX('#',@strIdMap,1)
set @nPos1 = CHARINDEX('*',@strIdMap,1)
set @nPos2 = CHARINDEX(',',@strIdMap,1)
set @Rid = CONVERT(int,SUBSTRING(@strIdMap,1,@nPos-1))
set @nPkg = CONVERT(int,SUBSTRING(@strIdMap,@nPos+1,@nPos1-@nPos-1))
set @nBegin = CONVERT(smallint,SUBSTRING(@strIdMap,@nPos1+1,@nPos2-@nPos1-1))
set @strIdMap = STUFF(@strIdMap,1,@nPos2,NULL)
--更新数据库
begin tran
-- 从 Wait表复制到Sending表
insert into tSentlog_sending select * from tSentLog_wait where RandId = @Rid
if @@error <> 0 OR @@rowcount =0
goto ERR
-- 更新SentTime,ItemCount字段
update tSentLog_sending set SentTime = GetDate(),ItemCount = @nPkg where RandId = @Rid
if @@error <> 0 OR @@rowcount =0
goto ERR
-- 删除Wait表中原记录
delete tSentLog_wait where RandId = @Rid
if @@error <> 0 OR @@rowcount =0
goto ERR
-- 建立RandId 与 SendId 映射关系
set @i = 0
while(@i < @nPkg)
begin
insert into tRandIdMap(SendId,RandId) values(@nBegin+@i,@Rid)
if @@error <> 0 OR @@rowcount =0
goto ERR
else
set @i = @i+1
end
commit tran
continue
ERR:
rollback tran
set @nRet = -1
END
-- 退出前取SendId 字段的最大值`
set @MaxSendId = @@identity
RETURN @nRet