需要帮助,了解如何在存储过程中捕获与执行sql脚本相关的任何错误。
select sopScript
from M_SopInsert
where soptype = @soptype and sopnumbe = @sopnumbe and lnitmseq = @lnitmseq
If result_count > 0 //if result from above sql query is >0
exec sopScript //loop through the record set and execute sopscript for every record.
注意:这里的sopscript包含以下脚本:
update customerMaster
set custname='abc'
where custid=100`"
我们就是这样做的:
将过程步骤封装在TRY
和TRANSACTION
中。然后,TRY
中的各个执行
DECLARE @lRollback bit=0
DECLARE @ErrTable TABLE (ErrNumber int,ErrSeverity int,ErrProc varchar(MAX),ErrLine int,ErrMsg varchar(MAX)) --table variable to collect errors.
BEGIN TRY -- outside begin try
BEGIN TRANSACTION -- wrap transaction
....
BEGIN TRY
...
END TRY
BEGIN CATCH
{ERROR CATCH - see below}
END CATCH
END TRY
BEGIN CATCH
SET @lRollback=1
{ERROR CATCH - see below}
ROLLBACK
BEGIN TRY
INSERT INTO errorTable (importId,errNumber,errSeverity,errProc,errLine,errMsg) --This is the db default error collection table
SELECT DISTINCT @importId,ErrNumber,ErrSeverity,ErrProc,ErrLine,ErrMsg FROM @ErrTable
END TRY
RETURN -1
END CATCH
任何时候你想在过程中发现错误,都可以使用这个ERROR CATCH
:
INSERT INTO @ErrTable (ErrNumber,ErrSeverity,ErrProc,ErrLine,ErrMsg)
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
最初误解了问题。
尝试使用
declare @sopScript varchar(1000)
select sopScript
into #ControlTbl
from M_SopInsert
where soptype = @soptype and sopnumbe = @sopnumbe and lnitmseq = @lnitmseq
while exists (select * from #ControlTbl)
begin
select top 1 @sopScript = sopScript
from #ControlTbl
begin try
exec executesql @sopScript = sopScript
end try
begin catch
*do something*
end catch
delete from #ControlTbl
where sopScript = @sopScript
end