hey guys welcome back to this SQL tutorial course. In the session we will
learn exception handling in SQL. So an error condition during a program
execution is called as an exception and the mechanism for resolving such an
exception is known as exception handling. And we can do exception handling in SQL
with the help of try catch blocks. So the try statement allows us to test a block
of statements while they are being executed and the catch block allows us to handle
any exception which occurs in the try block. Now let's have a look at the
syntax of the try catch block so we'll give the key words begin try and then give
the statements which are to be tested. After that will end the try block and begin
the catch block. Inside the catch block will either bring the error or rollback
the transaction and we'll end the catch block as well so let's go to SQL
server and do some exception handling so I'll be declaring two variables and
divide one of the variables with 0 so this needs to give us divided by 0
exception. So let's go ahead and handle this exception now. So we can declare a
variable SQL by using the declare statement so I'll type declared and then
use the @ symbol to declare value and I'll name this variable as var 1
and this is of integer type similarly let me go ahead and declare the second
variable I'll type declare and the name of this variable is value and the data type
is integer again. Now I'll begin the try block I'll type begin, try and then
inside this I will set the values for both value 1 and value 2. So I'll use a
set keyword and then the value which i ll be
assigning to while 1 state I'll put semicolon similarly let me
assign something to while 2 do as well so set @ while do now inside well do
what i ll basically do is divide while one with zero so now this statement should
basically give us the error so when we divide any integer value with the zero
we'll get an indefinite value so this is the statement which will be giving the
error. Now I will end the try block and try right now after this we'll begin the
catch block, begin catch and inside the catch block I will just print out
the error which occur in the try block and I will display the default error
message
and I will end the catch block as well
so let me hit execute now so default error message which you get is divided
by zero error encountered so what we basically did with the set of statements
was we encountered an error within the try block and we handled that error
inside the catch block so inside the catch block
we sent a default error message and the default error message was divided by
zero error encountered. Now we can also create user-defined messages so let's go
ahead and create some user-defined messages and for that purpose I will be
doing exception handling on top of the employee table so let me actually have a
glance at the employ table first. Select star from the name of the table is
employee right so we have this employee table right in front of us so now what
I'll do is I'll try to add the salary column and the name column now the
problem with this is we cannot add a numerical column with a string value so
let's handle this exception so I'll type begin try and then I will
give the select keyword and I will try to add e_salary column with the e_name
column and I want this from the employee table
I will end the try block after this I need to begin the catch block so begin catch
now inside this I will print a user-defined message so print and I
would want to print add a numerical value with a string
value I will end the cache block as well after this I will give the go statement
let me hit execute. This is the message which we get. We cannot
add a numerical value with the string value and we got this user-defined
message because we had encountered an error inside the try block. So this is
how we can do exception handling in SQL. Thanks for attending the session. Let's meet
in the next class