Loggin Error

microsoft.public.sqlserver.programming

16 Sep 2005 12:59 AM

Rizwan

I am writing a stored procedure to perform a proces. This stored procedure
will be scheduled as a sql server job to be run every hour. It is also
possible to run it manually in Query Analyzer. Or even call it from EJB (but
thats down the road).

1) In this sp first I am doing some checks. If any of the check is failed
then i want to abort running the sp. This is not an error really; its just
that the conditions are still not met for that process to run. I do want to
log what check gets failed (a message).

2) If checks are passed then i do inserts and updates on table(s). If any of
them fails then i want to rollback transaction and log the error (a
message).

So I start like this :

CREATE PROCEDURE MyProcess
AS
BEGIN
declare @FLAG_CommitTransaction tinyint, @v_return_code numeric(18,0),
@v_check_pass bit

select @FLAG_CommitTransaction = 0, @v_return_code = 0

if ( @@trancount = 0 )
begin
begin transaction MyProcess
set @FLAG_CommitTransaction = 1
end
.....

if ( @FLAG_CommitTransaction = 1 )
begin
commit transaction MyProcess
end
return @v_return_code

error_handler:
if ( @FLAG_CommitTransaction = 1 )
begin
rollback transaction MyProcess
end
return @v_return_code

END

For 1) I am doing like this :

@v_check_pass = ... -- check
if ( @v_check_pass = 0 )
begin
set @v_return_code = -1
goto error_handler
end

For 2) I am doing like this :
update shift set .... -- execute update Statement
set @v_return_code = @@error
if ( @v_return_code <> 0 )
begin
goto error_handler
end

My question is if this is right approach? I am not able to log a message
with this approach. How can I do that?

Thanks

16 Sep 2005 9:42 AM

R.D

Rizwan
I think you are from vb back ground.
Dont be surprised to here that there is NO error handling mechanism as such
in SQL SERVER programming. Yet there is a way out.
In your case follow these steps:
1) Add custom error message with severity level more than 16 and raise error
2)use @@error to return the error code and store it immediately into a
variable.
3) as severity is higher it will retuurn to front end and also logs
4) if you want it to log to another table, use custom message and insert
that in to table then roll back and return
see BOL
and there is good article on error handling google for that
Regards
R.D

Show quote

"Rizwan" wrote:

> I am writing a stored procedure to perform a proces. This stored procedure
> will be scheduled as a sql server job to be run every hour. It is also
> possible to run it manually in Query Analyzer. Or even call it from EJB (but
> thats down the road).
>
> 1) In this sp first I am doing some checks. If any of the check is failed
> then i want to abort running the sp. This is not an error really; its just
> that the conditions are still not met for that process to run. I do want to
> log what check gets failed (a message).
>
> 2) If checks are passed then i do inserts and updates on table(s). If any of
> them fails then i want to rollback transaction and log the error (a
> message).
>
> So I start like this :
>
> CREATE PROCEDURE MyProcess
> AS
> BEGIN
> declare @FLAG_CommitTransaction tinyint, @v_return_code numeric(18,0),
> @v_check_pass bit
>
> select @FLAG_CommitTransaction = 0, @v_return_code = 0
>
> if ( @@trancount = 0 )
> begin
>   begin transaction MyProcess
>   set @FLAG_CommitTransaction = 1
> end
> .....
>
> if ( @FLAG_CommitTransaction = 1 )
> begin
> commit transaction MyProcess
> end
> return @v_return_code
>
> error_handler:
> if ( @FLAG_CommitTransaction = 1 )
> begin
>   rollback transaction MyProcess
> end
> return @v_return_code
>
> END
>
> For 1) I am doing like this :
>
> @v_check_pass = ... -- check
> if ( @v_check_pass = 0 )
> begin
>   set @v_return_code = -1
>   goto error_handler
> end
>
>
> For 2) I am doing like this :
> update shift set .... -- execute update Statement
> set @v_return_code = @@error
> if ( @v_return_code <> 0 )
> begin
>   goto error_handler
> end
>
> My question is if this is right approach? I am not able to log a message
> with this approach. How can I do that?
>
> Thanks
>
>
>
>
>

16 Sep 2005 10:12 PM

Hugo Kornelis

On Thu, 15 Sep 2005 20:59:46 -0400, Rizwan wrote:

>I am writing a stored procedure to perform a proces. This stored procedure
>will be scheduled as a sql server job to be run every hour. It is also
>possible to run it manually in Query Analyzer. Or even call it from EJB (but
>thats down the road).
>
>1) In this sp first I am doing some checks. If any of the check is failed
>then i want to abort running the sp. This is not an error really; its just
>that the conditions are still not met for that process to run. I do want to
>log what check gets failed (a message).
>
>2) If checks are passed then i do inserts and updates on table(s). If any of
>them fails then i want to rollback transaction and log the error (a
>message).

Hi Rizwan,

I'm not sure what EJB is. But based on yoour description, I guess the
stored procedure should roughly look like this:

CREATE PROCEDURE MyProcess
AS
IF -- insert 1st check here; negate logic (TRUE means "check failed")
BEGIN
INSERT INTO LogTable (Column1, Column2, ...)
VALUES (Value1, Value2, ...)
RETURN
END

IF -- insert 2nd check here; negate logic (TRUE means "check failed")
BEGIN
INSERT INTO LogTable (Column1, Column2, ...)
VALUES (Value1, Value2, ...)
RETURN
END

DECLARE @err_code INT
BEGIN TRANSACTION
-- insert 1st modification statement here
SET @err_code = @@ERROR
IF @err_code <> 0
BEGIN
ROLLBACK TRANSACTION
INSERT INTO LogTable (Column1, Column2, ...)
VALUES (Value1, Value2, ...)
RETURN
END

-- insert 2nd modification statement here
SET @err_code = @@ERROR
IF @err_code <> 0
BEGIN
ROLLBACK TRANSACTION
INSERT INTO LogTable (Column1, Column2, ...)
VALUES (Value1, Value2, ...)
RETURN
END

COMMIT TRANSACION

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)