Welcome to weblogs.com.pk Sign in | Join | Help

Error Handling In TSql

This post discusses about error handling methodologies in TSql, the native language of Microsoft’s SQL Server.

 

Error Handling is important in any application development. This applies in coding stored procedures with TSql. Stored procedures have an importance of their own, they not only give us extra bits of performance, but help us introducing an abstraction layer, that can be used for polymorphism and security purposes. Explaining them, I guess needs a separate post J

 

Anyways coming back to the point, let me introduce a demo stored procedure based on which I will try to explain the concepts.

 

We have two tables, Users and UserDetails and while creating a new user, we need to add the information in both tables. Something like

 

declare @UserIndex int;

insert into users (userid, password) values (@UserID, @Password);

select @UserIndex=Scope_Identity();

insert into userdetails (userindex, firstname, lastname)

values (@UserIndex, @FirstName, @LastName);

 

As there are two statements involves, we should use transactions to improve dependability.

 

declare @UserIndex int;

begin transaction

insert into users (userid, password) values (@UserID, @Password);

select @UserIndex=Scope_Identity();

insert into userdetails (userindex, firstname, lastname)

values (@UserIndex, @FirstName, @LastName);

commit transaction

 

The problem now is that even if we use transaction, it will not care the statement succeeded or failed. For instance, we try to insert into users the same userid that already exists. To handle this we need Error Handling. Lets add few more code

 

declare @UserIndex int;

begin transaction

insert into users (userid, password) values (@UserID, @Password);

if @@error<>0

begin

rollback transaction;

return;

end

select @UserIndex=Scope_Identity();

insert into userdetails (userindex, firstname, lastname)

values (@UserIndex, @FirstName, @LastName);

commit transaction

 

What if the second insertion fails? Let’s add error handling for that too.

 

begin transaction

insert into users (userid, password) values (@UserID, @Password);

if @@error<>0

begin

rollback transaction;

return;

end

select @UserIndex=Scope_Identity();

insert into userdetails (userindex, firstname, lastname)

values (@UserIndex, @FirstName, @LastName);

if @@error<>0

begin

rollback transaction;

return;

end

commit transaction

 

You must have realized that introducing error handling has made our code ugly, isn’t it? Furthermore, if we have multiple statements around which we need error handling, the chance of forgetting error handling is more. Here is one of the approaches that I follow, its even more messy!

 

--Dont send rows affected to client

set nocount on;

--Automatically rollback transactions on error

set xact_abort on;

--Error handling

declare @@errorMessage nvarchar(500);

begin transaction

insert into users (userid, password) values (@UserID, @Password);

if @@error<>0

begin

select @@errorMessage=’Unable to add user’;

goto ThrowError;

end

select @UserIndex=Scope_Identity();

insert into userdetails (userindex, firstname, lastname)

values (@UserIndex, @FirstName, @LastName);

if @@error<>0

begin

select @@errorMessage=’Unable to add user’;

goto ThrowError;

end

commit transaction

return 0;

ThrowError:

      --print @@errorMessage;

      raiserror (@@errorMessage, 16, 1);

      return -1;

 

Well unfortunately, we have to live with it, and wait until the Yukon release, the next version of SQL server. They have added try/catch approach in TSql that will remove this ugliness in the code caused due to such error handling. Here is the Yukon TSql equivalent

 

declare @UserIndex int;

set XACT_ABORT on;

begin try

begin transaction

insert into users (userid, password) values (@UserID, @Password);

select @UserIndex=Scope_Identity();

insert into userdetails (userindex, firstname, lastname)

values (@UserIndex, @FirstName, @LastName);

commit transaction

end try

begin catch TRAN_ABORT

if @@trancount>0 rollback

end catch

Published Wednesday, April 28, 2004 12:02 PM by khurram
Filed under:

Comments

No Comments

New Comments to this post are disabled