Need help to execute sql scripts within stored procedure

Question

Need help as how I can trap any errors related to executing a sql script in a stored procedure.

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.

Note: sopscript here contains scripts like :

update customerMaster 
set custname='abc' 
where custid=100`"

Please check this [article](http://technet.microsoft.com/en-us/library/aa175920%28v=sql.80%29.aspx) — bastos.sergio, Aug 26 '13 at 14:23
Please read this article on SQL cursors: http://technet.microsoft.com/en-us/library/ms180169.aspx — davids, Aug 26 '13 at 14:51
@davids: No, I was more interested in trapping any errors. Also let us say if the resultset has 5 rows (meaning I get 5 sopScripts) and if one sopScript fails, then I need to rollback all 5 and return with error. — Anirudh, Aug 26 '13 at 14:55
It would be more helpful if your question title reflected the actual question (e.g How do I trap errors in a stored procedure) and if you demonstrate what you have actually done to try and solve it yourself. In any case, please see my answer below. — davids, Aug 26 '13 at 16:00

davids · Answer 1 · 2013-08-26T15:59:23.563

This is how we do it:

Wrap the procedure steps in a TRY and TRANSACTION. Then the individual executions in a 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

Anytime you want to catch an error in the procedure, use this 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;

sam yi · Accepted Answer · 2013-08-26T14:28:36.683

0

Misread the question originally.

try using

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

edited Aug 26 '13 at 14:28

answered Aug 26 '13 at 14:22

sam yi

4,806
1
29
40

I need to loop through the result set and then execute the sopscript for every record by capturing any errors. Also, I think you mentioned "top 1". – Anirudh Aug 26 '13 at 14:28
Thanks for the edit. How do I loop through 'n' records? Also I am more interested with examples of the catch and how I can control any errors. As sopScript is a sql script, I want to make sure if it s a good SQL script and when it gets executed, if there are any errors, I do something. – Anirudh Aug 26 '13 at 14:32
How you want to handle the error is up to you. Most log to a separate table or alert operator... etc. Or are you trying to prevalidate the script before running? – sam yi Aug 26 '13 at 14:42
I am more interested in trapping any errors. Also let us say if the resultset has 5 rows (meaning I get 5 sopScripts) and if one sopScript fails, then I need to rollback all 5 and return with error. – Anirudh Aug 26 '13 at 14:56
Talk about scope creep. – sam yi Aug 26 '13 at 15:26
Sorry if I was not clear. Also I didn't downvote your answer. I will be marking it answered shortly. – Anirudh Aug 26 '13 at 15:34
dont really care about the downvote. just didn't want to sit here all day answering questions. in the future, it would be more helpful if you can be more specific with your questions. scope creep is a real problem in software development.. ;) – sam yi Aug 26 '13 at 18:47

Need help to execute sql scripts within stored procedure

2 Answers2