jump to navigation

Using THROW statement in SQL Server 2012 July 20, 2012

Posted by fofo in general .net, Sql Server, SQL Server 2012.
trackback

In this post I would like to demonstrate with a hands-on example how to use the new THROW statement available to us for error handling in SQL Server 2012.

Well, most of you know that we have TRY-CATCH statements for exception handling since SQL Server 2005.I have blogged about error handling and exceptions in this post.

I am sure that most of you are familiar with TRY-CATCH statement but before I go on with the actual example I will explain it. Inside the TRY block all the instructions that might cause an exception are included. When an exception occurs from one of the statements that are inside in the TRY block, the control is handed to the CATCH block, where the exception can be handled (by printing a message to the user, log the error e.t.c)

In SQL Server 2012 we have a new statement, THROW which can be used as an alternative of RAISERROR but most importantly we can re-throw the original error that occurred back to the client, so it can be handled at the application level.

You can use any edition of SQL Server of 2012 you want for this example. SQL Server Express edition will do just fine.Download the Express edition, here.

Connect to the instance of the SQL Server.

Before I move on to demonstrate the use of THROW statement, there are some things we must note

  • The line (statement) before the THROW statement must end with semicolon (;)
  • All exceptions raised by THROW statement will have a severity of 16
  • THROW statement doesn’t require an error number but if you choose to use one it should be greater than 50.000

The syntax is pretty basic:

THROW (error_number,message,state)

You must define these parameters only if you use THROW outside the CATCH block.

1) THROW outside a catch block

In a new query window type and execute the code below


THROW 12767, 'I am a new statement in SQL Server 2012', 1;
THROW 52767, 'I am a new statement in SQL Server 2012', 1;

The first statement will fail since the error number is less than 50.000. The second statement will succeed.

2) THROW inside a catch block

In a new query window type  the code below

USE tempdb;
go

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

INSERT INTO persons VALUES (1,'Michael','Owen','122 liverpool street','liverpool')
INSERT INTO persons VALUES (2,'Steven','Gerrard','123 liverpool street','liverpool')

SELECT * FROM persons

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

BEGIN TRY
BEGIN TRANSACTION
INSERT INTO orders VALUES (2,12345,2)
INSERT INTO orders VALUES (4,12345,3)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
RAISERROR ('we have an error',17,21);
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH

I create a table(Persons) in tempdb and then I insert some values in it.Then I create a second table that is related to the first one with a foreign key constraint.

Then I use an insert statement for the second table (Orders -That I know will violate the foreign key constraint) inside a TRY-CATCH block and use RAISERROR function to raise a custom error and then rollback the transaction.

Execute the code and note the results and messages.

Now let’s re-write the INSERT statement above using the THROW statement.

In a new query window type and execute the code below

USE tempdb;
go

BEGIN TRY
BEGIN TRANSACTION
INSERT INTO orders VALUES (2,12345,2)
INSERT INTO orders VALUES (4,12345,3)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'There was an error when inserting the records';
THROW;
END CATCH

In my case the results are

The actual error that caused the exception in the first place, appears (is re-thrown) again.

Please note that there is not (yet) FINALLY statement.

Hope it helps!!!

Comments»

1. Dot Net Rules : Using THROW statement in SQL Server 2012 - July 20, 2012

[…] handling since SQL Server 2005.I have blogged about error handling and exceptions in this post. (read more) Share Posted: Παρασκευή, 20 Ιουλίου 2012 10:39 μμ από το […]

2. SQL Server 2012 – Transact-SQL Enhancements – Learning Resources « Datta's Ramblings on Business Intelligence 'N' Life - July 29, 2012

[…] Using THROW statement in SQL Server 2012 […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: