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`"

Answer 1

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