June 16, 2005 at 8:21 am
The way you're doing it is fine. If the error is too severe, the server will rollback that transaction. Then you can check if any transactions are still opened after the execution, and rollback ten if there are.
Unless someone else knows better (not my domain of expertise).
June 16, 2005 at 4:25 pm
I wondering if you can give a sql sample where you can capture an error??
I still believe that it will never print your error message.
June 17, 2005 at 6:07 am
Sure :
GO
CREATE PROCEDURE dbo.Test @Dividor as int
AS
SET NOCOUNT ON
Declare @Er as int
begin tran
Select 10 / @Dividor
Set @Er = @@Error
if @Er 0
begin
print 'There''s an error'
rollback tran
end
else
begin
print 'No problem -- commiting tran'
commit tran
end
SET NOCOUNT OFF
GO
exec dbo.test 10
/*
1
No problem -- commiting tran
*/
exec dbo.test 0
/*
Serveur : Msg 8134, Niveau 16, État 1, Procédure Test, Ligne 6
Division par zéro.
There's an error
*/
GO
DROP PROCEDURE test
GO
June 18, 2005 at 9:07 am
I know the method I use can't be easily applied to most systems already developed but this is what I do.
Since the default return value from a sproc is 0, I code all my procedures to Return 1 on a condition of success. If the calling procedure/client doesn't get a Return of 1 assume an error has occurred. Of course I still do all the normal error checking and Raiserror stuff after each trip to the database.
Hope that helps someone.
-ron
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply