Debugging stored procedures

I have complete error handling and printing the error after every insert or update statements or after calling another procedure.

But somehow when executing the proc it is not printing the error.

The query analyzer shows a general message 'Query batch completed with errors'

All the logic seems to be working properly, but this message is bothering me. Why is this message displayed if everything is run correctly [or] is something wrong ?

Example:

[code]

/*************************************************************
** Error Handling
**************************************************************/

SELECT @.rowcount = @.@.rowcount
,@.error = @.@.error
,@.short_msg = 'Error Creating MCTM. - AG_SP_FAC_MCTN_INSERT'
,@.long_msg = 'Error in executing proc AG_SP_FAC_MCTN_INSERT'
,@.resolution_msg = 'Stored procedure error. Contact Technical Support for fix'
,@.log_cd = 'AG.CONV.ERR' + convert(char,@.exec_seq_no)
,@.log_level = 'O'
,@.log_severity = 3

IF (@.error <> 0)
BEGIN

EXEC amgrp_conv..AG_SP_LOG
@.LOG_CD = @.log_cd
,@.LOG_DTM = @.log_dtm
,@.LOG_LEVEL = @.log_level
,@.LOG_SEVERITY = @.log_severity
,@.APP_NAME = @.app_name
,@.SHORT_MSG = @.short_msg
,@.LONG_MSG = @.long_msg
,@.RESOLUTION_MSG = @.resolution_msg
,@.MAIN_STORED_PROC_NAME = @.main_stored_proc_name
,@.STEP_STORED_PROC_NAME = @.step_stored_proc_name
,@.SYBASE_CD = @.error

PRINT 'ERROR=' + convert(varchar(255),@.error)

ROLLBACK TRANSACTION TRAN_PRAC_PAR
CLOSE prac_par_cursor
DEALLOCATE prac_par_cursor
RETURN @.failure

END

[/code]

IF (@.@.error <> 0) --this should be @.@.error @.error from previous statement is unreliable
BEGIN

EXEC amgrp_conv..AG_SP_LOG
@.LOG_CD = @.log_cd
,@.LOG_DTM = @.log_dtm
,@.LOG_LEVEL = @.log_level
,@.LOG_SEVERITY = @.log_severity
,@.APP_NAME = @.app_name
,@.SHORT_MSG = @.short_msg
,@.LONG_MSG = @.long_msg
,@.RESOLUTION_MSG = @.resolution_msg
,@.MAIN_STORED_PROC_NAME = @.main_stored_proc_name
,@.STEP_STORED_PROC_NAME = @.step_stored_proc_name
,@.SYBASE_CD = @.error

PRINT 'ERROR=' + convert(varchar(255),@.error)

ROLLBACK TRANSACTION TRAN_PRAC_PAR
CLOSE prac_par_cursor
DEALLOCATE prac_par_cursor
RETURN @.failure

END

|||

Not quite getting it.

Does this mean @.@.error may not return anything. My understanding is it will be '0' if success and any other number if its an error

In other words, the following doesn't work ?

declare @.error int

select @.error = @.@.error

if (@.error <> 0)

begin

end

|||

QUOTED:

Not quite getting it.

Does this mean @.@.error may not return anything. My understanding is it will be '0' if success and any other number if its an error

In other words, the following doesn't work ?

declare @.error int

select @.error = @.error + (other select clause ) --<-- what if the error lies in here

if (@.error <> 0)

begin

end

|||

thanks joeydj,

ok i see...

@.@.error is for select statements too ?

i can check the selects, but its a standard select as shown above and there seems to be no error there.

|||

thats a wild guess anyway.

|||

can you please check if this line is valid

,@.log_cd = 'AG.CONV.ERR' + convert(char,@.exec_seq_no)

|||

still not getting it try this. this one should do it.

hahaha

declare @.error int

select @.error=0

SELECT @.rowcount = @.@.rowcount
,@.error = @.@.error
,@.short_msg = 'Error Creating MCTM. - AG_SP_FAC_MCTN_INSERT'
,@.long_msg = 'Error in executing proc AG_SP_FAC_MCTN_INSERT'
,@.resolution_msg = 'Stored procedure error. Contact Technical Support for fix'
,@.log_cd = 'AG.CONV.ERR' + convert(char,@.exec_seq_no)
,@.log_level = 'O'
,@.log_severity = 3

IF (@.error <> 0)
BEGIN

EXEC amgrp_conv..AG_SP_LOG
@.LOG_CD = @.log_cd
,@.LOG_DTM = @.log_dtm
,@.LOG_LEVEL = @.log_level
,@.LOG_SEVERITY = @.log_severity
,@.APP_NAME = @.app_name
,@.SHORT_MSG = @.short_msg
,@.LONG_MSG = @.long_msg
,@.RESOLUTION_MSG = @.resolution_msg
,@.MAIN_STORED_PROC_NAME = @.main_stored_proc_name
,@.STEP_STORED_PROC_NAME = @.step_stored_proc_name
,@.SYBASE_CD = @.error

PRINT 'ERROR=' + convert(varchar(255),@.error)

ROLLBACK TRANSACTION TRAN_PRAC_PAR
CLOSE prac_par_cursor
DEALLOCATE prac_par_cursor
RETURN @.failure

END