Handling errors in/from stored procs

[onedunpark]

Hi,


I have an Insert trigger that amongst other things fires a Stored Proc to perform additional updating. One of the updates in the Stored Proc tries to update a column with data that is too long to fit. The Stored proc, when run in isolation as follows:

declare @vsetindexno int
set @vsetindexno = 592

EXEC fitz_SQLTriggerUpdate @vsetindexno

runs perfectly fine and handles the error internally, as I would expect it to.

When ran from my Insert Trigger though (or from a simple looping program - passing the appropriate variable), using exactlt the same syntax as above, the trigger fails telling me "String or binary data would be truncated. The statement has been terminated." and the Stored Proc/Trigger/looping program aborts immediately.

The Stored Proc can deal with this internally, and carries on processing when run "stand-alone" so I'm guessing the question is

"how do I call a Stored Proc, ensuring that any errors encountered are handled by the proc and do not bring down my external routine?"

Apologies if this is painfully obvious to one and all

Any and all replies greatly appreciated

Regards

Steven

 

[onedunpark]

Hi again,

I should have said that the failing update is actually an UPDATE command created as a string (@SQLstring) and run as follows:

exec sp_executesql @SQLstring

I do know that I can call my SP as follows:

declare @vretcode int, @vsetindexno int
set @vsetindexno = 592
exec @vretcode = fitz_SQLTriggerUpdate @vsetindexno
if @vretcode <> 0

but the problem is I don't have enough control at the sp_executesql level to be able to pass a fail code back. It blows out on me and I don't know how to handle it at that level.

If all of that makes sense......

Thanks again

Steven