Kick off procs

Hello. I was wondering what is the best way to kick off multiple procs
trapping the ones that had an error. Here's an example of what I came up
with.
alter procedure dbo.testError
@.problem int = 0 OUTPUT
AS
set nocount on
print 'start'
Declare @.error_msg int
set @.error_msg = 0
set @.error_msg = (Select count(*) from notable)
print 'yo'
select @.error_msg = @.@.error
IF @.error_msg != 0 GOTO handle_error
return @.Problem
handle_error:
set @.Problem = @.error_msg + @.Problem
print @.Problem
-- this is where I would kick off the processes in sequence
declare @.msg int
EXEC @.msg = testError
print 'testing = ' + convert(varchar(20), @.msg)
on testError I have an output variable. when you kick off the proc in my
kick off code, the print line never gets executed. and the subroutine in the
main proc never gets called. Really all I want to do is kick off a list of
sprocs and write to a table wether it was a success or not, then go on
kicking off the next sproc. Also, is it nessecarry to alter all my existing
sprocs to have an output variable and catch @.@.error on all calls to the db,
if not that would be ideal.
just wondering how everyone handles trapping errors with a kick off sproc,
and why this is not working.
Thanks,
RobRobert H wrote:
> Hello. I was wondering what is the best way to kick off multiple procs
> trapping the ones that had an error. Here's an example of what I came
> up with.
> alter procedure dbo.testError
> @.problem int = 0 OUTPUT
> AS
> set nocount on
> print 'start'
> Declare @.error_msg int
> set @.error_msg = 0
> set @.error_msg = (Select count(*) from notable)
> print 'yo'
> select @.error_msg = @.@.error
> IF @.error_msg != 0 GOTO handle_error
>
> return @.Problem
> handle_error:
> set @.Problem = @.error_msg + @.Problem
> print @.Problem
>
> -- this is where I would kick off the processes in sequence
> declare @.msg int
> EXEC @.msg = testError
> print 'testing = ' + convert(varchar(20), @.msg)
>
> on testError I have an output variable. when you kick off the proc in
> my kick off code, the print line never gets executed. and the
> subroutine in the main proc never gets called. Really all I want to
> do is kick off a list of sprocs and write to a table wether it was a
> success or not, then go on kicking off the next sproc. Also, is it
> nessecarry to alter all my existing sprocs to have an output variable
> and catch @.@.error on all calls to the db, if not that would be ideal.
> just wondering how everyone handles trapping errors with a kick off
> sproc, and why this is not working.
> Thanks,
> Rob
You're using a return value and an OUTPUT paramer in some interleaved
fashion, but they are not compatible.
You can return an INT return value using the following exec code and a
return statement:
DECLARE @.iRet INT
EXEC @.iRet = dbo.MyProc
PRINT @.iRet
You can use an OUTPUT parameter of most any data type and access the
value with the following exec code:
DECLARE @.NewName VARCHAR(50)
EXEC dbo.MyProc @.NewName OUTPUT
PRINT @.NewName
Or to do both:
DECLARE @.iRet INT
DECLARE @.NewName VARCHAR(50)
EXEC @.iRet = dbo.MyProc @.NewName OUTPUT
SELECT @.iRet, @.NewName
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi
Your print statement will reset @.@.ERROR therefore it is not going to give
you an error value.
You may want to read
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
John
"Robert H" wrote:

> Hello. I was wondering what is the best way to kick off multiple procs
> trapping the ones that had an error. Here's an example of what I came up
> with.
> alter procedure dbo.testError
> @.problem int = 0 OUTPUT
> AS
> set nocount on
> print 'start'
> Declare @.error_msg int
> set @.error_msg = 0
> set @.error_msg = (Select count(*) from notable)
> print 'yo'
> select @.error_msg = @.@.error
> IF @.error_msg != 0 GOTO handle_error
>
> return @.Problem
> handle_error:
> set @.Problem = @.error_msg + @.Problem
> print @.Problem
>
> -- this is where I would kick off the processes in sequence
> declare @.msg int
> EXEC @.msg = testError
> print 'testing = ' + convert(varchar(20), @.msg)
>
> on testError I have an output variable. when you kick off the proc in my
> kick off code, the print line never gets executed. and the subroutine in t
he
> main proc never gets called. Really all I want to do is kick off a list of
> sprocs and write to a table wether it was a success or not, then go on
> kicking off the next sproc. Also, is it nessecarry to alter all my existin
g
> sprocs to have an output variable and catch @.@.error on all calls to the db
,
> if not that would be ideal.
> just wondering how everyone handles trapping errors with a kick off sproc,
> and why this is not working.
> Thanks,
> Rob
>
>