How to trap errors from nested stored procedures??
-
How should I trap errors from nested stored procedures??
Example:
create procedure usp_T2
as
declare @a int, @b-2 char(10), @C int, @err int
set @a = 123456
set @b-2 = '123'
set @err = @@error
if isnull(@c,0) <> 0
return @C
else
return @err
go
create procedure usp_T1
as
declare @rc int
execute @rc = usp_T2
if @rc = 245
print 'You Idiot, @b-2 must be a numeric value!!'
else
print 'The answer is ' + convert(varchar, @rc)
go
set nocount on
exec usp_T1
Works Fine:
The answer is 1003
If I set @b-2 to '123a' this is the result:
Server: Msg 245, Level 16, State 1, Procedure usp_T2, Line 8
Syntax error converting the varchar value '123a ' to a column of data type int.
This is a very simple example, my real life problem is 1105 space errors!
-
Hi Ian,
You are getting the error message because of these lines -
set @b-2 = '123a'
You cant set the integer value @b-2 to the string '123a' - it will bomb immediately. @b-2 needs to be declared intitially as a varchar.
If @b-2 cant be converted to an integer value, @a cant be divided by @b-2 and the proc will bomb. If there is any chance @b-2 may come in as anything other than an Integer (which there shouldn't be) you need to handle this differently e.g.
IF ISNUMERIC(@b) = 1
BEGIN
END
ELSE
BEGIN
set @err = @@ERROR --Use your own value as @@ERROR wont have a value here......
END
Also, if you want to output the value of @b-2 in your error message - 'You Idiot, @b-2 must be a numeric value!!' - wont work, it will send what you see not the value of @B.
NOTE - I am taking it as red you realise cant use a variable declared in sproc1, to output a value in sproc2......
Use -
print 'You Idiot, ' + Cast(@b as varchar(10)) + ' must be a numeric value!!'
To put the value into the string and make the message meaningful.
Have fun
Steve
We need men who can dream of things that never were.
-
OK, Here is my real life problem!.
The called sp does the following + a lot of other stuff:
.
.
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
SELECT @sqlstring = 'DBCC DBREINDEX(' + @tablename + ', " ", 0, SORTED_DATA_REORG)'
EXEC (@sqlstring)
FETCH NEXT FROM tnames_cursor INTO @tablename
END
DEALLOCATE tnames_cursor
.
.
What I want to be able to do is trap 1105 errors as we are running pretty close to the wind disk space wise. This sp just terminates reporting the 1105 error. I want to be able to trap this error from my calling sp and handle it a little more elegantly. I should mention the above is in a sp written by the application vendor who do regular updates, so I would prefer to do all error trapping in my calling sp
Ian Scott
-
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
SELECT @sqlstring = 'DBCC DBREINDEX(' + @tablename + ', " ", 0, SORTED_DATA_REORG)'
EXEC (@sqlstring)
FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor <<<<< MISSING
DEALLOCATE tnames_cursor
HTH
* Noel -
Timeout! What I am attempting to explain with examples is:
"Can I trap ANY error (missing statements, incorrect data types, not testing for Nulls, no more space etc etc) in a sp called from another sp without modifying the called sp??".
If someone gives me a sp that is 50,000 lines of code, can I call it from another sp and handle any potential errors generated by the called sp in the calling sp??
-
The Level 16 errors seem to be the toughest to trap within nested stored procedures because when they occur your procedure immediately exists to the calling procedure or program. Other errors are more easilly trapped by using output parameters.
Example:
1. Make the Main stored procedure a transaction
create procedure [dbo].[main]
(
a varchar(10),
saveERR integer = 0 output
)
as
declare
@ERR integer,
@tmpERR integer
begin tran
EXEC @ERR = spr_CallMe
@calledA= @a,
@saveERR = @saveERR output --(not necessary but can be used for other errors ie logic errors)
/***** FOLLOWING @tmpERR is where you will trap the Level 16 errors from the called procedure. The @ERR is the return value which you can set from the called procedure */
set @tmpERR = (select @@error)
if @tmpERR <> 0 begin set @saveERR = @tmpERR goto ERROR_PROCESS END
if (@ERR <> 0 or @saveERR <> 0) goto ERROR_PROCESS
-- other processing or procedure calls...
--checking for errors to rollback entire transaction
ERROR_PROCESS:
if (isnull(@saveErr,isnull(@ERR,0)) <> 0)
begin
rollback
print 'rolling back...'
return isnull(@saveERR,@ERR)
end
else
begin
commit
print 'committing...'
return 0
end
/* here's the called procedure
*/
create procedure spr_CallMe
(
@calledA varchar(10),
@saveERR integer = 0 output
)
as
--code here or call other sprocs...
ERROR_PROCESS:
if (isnull(@saveErr,isnull(@ERR,0)) <> 0)
begin
print 'ERROR...'
return isnull(@saveERR,@ERR) -- becomes the value @ERR from callng sproc
end
else
begin
print 'good...'
return 0 -- becomes the value of @ERR from calling sproc
end
/* Hope this helps ... steve*/
-
Ian,
you omitted the select part from your reindex cursor, but I think it's fair to say that what it does, is to run DBREINDEX on a number of tables, correct?
Since it's all in a cursor, it's also all within the same batch, or transaction. DBREINDEX can also be taxing on the log, thus demanding that there is sufficient space to play with. And that's what you haven't got.
If disk is too small to handle the needed logsize, you're never going to get the cursor go all through the way it is - you must change it. It's not that difficult, though. What you need to do is to split the work, so as you reindex the tables one by one, or a few at a time, and either truncate or backup the log in between. (depends on your recovery model which is best)
In any case, you must change the 'all tables in one go' to 'one or only a few tables in each go'..
/Kenneth
-
I'm not feeling well, and not thinking clearly, so if I'm way off base, just ignore me, ok?
I think that what you are asking for is not how to fix the code that you have posted, but rather how to write a procedure "A" so that it will trap and gracefully handle any errors produced by procedure "B" which "A" calls with an EXECUTE statement. If I'm wrong, I apologize, and this is where you can quit reading!
The answer to my interpretation of your question is "it depends". Procedure "B" must be written in such a way to gracefully handle errors and provide the return code that Procedure "A" is looking for. If Procedure "B" has no error checking, its possibly just gonna bomb, and you'll have no chance to process the error.
my 1.5 cents
(take it with a couple tylenol extra strength and call me in ... nevermind)
Steve
-
Steve (hoo-t),
You have answered my question and confirmed my suspisions!!! It is imposible to trap ALL errors in a called procedure from a calling procedure.
Regards, Ian Scott
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply