Error handling in sql server Part 56 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to prism technologies I am
venket this is part 55 of sequel server
in this session we'll learn about
handling errors in sequence over 2005
and later versions before continuing
with the session I strongly recommend to
watch part 18 and 55 of this video
series with the introduction of
try-catch blocks in sequence of a 2005
error handling in sequence server is now
very much similar to programming
languages like C sharp and Java in
sequel server 2000 to handle errors we
use that error system function in fact
we have seen in part 55 of this video
series how to handle errors using a
Tutera system function if you haven't
watched that part I would strongly
encourage you to do so before continuing
with the session in sequel server 2005
and later we can make use of this try
catch construct to handle errors in fact
handling errors with try-catch construct
is much easier than handling errors with
edit header system function today for
this demo we'll be making use of TBL
product and TBL product sales tables the
same tables that we have worked with in
part 55 of this video series TBL product
table is like an inventory table which
contains the names of the products and
their respective unit prices and the
quantity available in store product ID
is the primary key for this table and
TBL product sales table contains
information about the products that we
sold so if you look at the columns
products sales ID is the primary key
product ID is the ID of the product that
we have sold and quantity sold is
obviously the quantity that we sold so
whenever we sell a product you know
there are two things that should happen
first we should deduct the quantity that
we are selling from TBL product table
next we should record the transaction in
TBL product sales table let's understand
this with an example
now at the moment there are 50 desktops
available now let us say I want to sell
five desktops so the first thing we have
to do is we have to deduct from 50 the
five desktops so we should update the
quantity available
column in TBL product table next we need
to insert a new row into TBL product
sales table the product ID that we are
selling is number two because that's the
desktop so number two and the point D
sold is five so in brief whenever we
make a transaction whenever we sell a
product you know it should first update
TBL product table and insert a new row
into TBL product sales table now let's
look at a stored procedure which is
capable of doing that now this is
essentially the same store procedure
that we have worked with in part 55 of
this video series so we have a procedure
called SP sell product which takes two
parameters product ID and quantity to
sell both of them of type integer the
first thing the stored procedure should
do is to check the stock available for
the product that we want to sell now
let's say here we have the stock
available variable of type integer into
this variable we are loading the
quantity that's available for the
product that we want to sell select EDD
stock available is equal to quantity
available from TBL product where product
is the product ID is equal to whatever
is the product ID that we are passing in
into this stored procedure now the next
important thing is to check ok
if the stock available is less than the
quantity that we want to sell then we
are in a problem we cannot continue with
that transaction so we have to throw an
error back to the calling application
stating that there is no end of stock
available and that's what we are doing
and the way we are doing it is by using
Gray's error function so this raised
error function basically takes three
parameters the first parameter is the
error message the second parameter is
error severity in most cases when we
want to create and return custom errors
we use a severity level of 16 which
indicates that these are general error
errors that can be corrected by the end
user and the final parameter is the
error State which is a number between 1
and 25 raised error only generate errors
with state from
one through one to seven in our example
for the error state we are using a value
of one okay
on the other hand if there is enough
stock available we come into the else
block and within the else block the
first thing that we want to do is to
update the TBL product table not the
quantity available column so update TBL
product set quantity available is equal
to whatever is the quantity available
from that subtract the quantity that we
are selling and this is nothing but the
parameter that we are passing in
whatever is the quantity that we want to
sell where product ID is equal to at
product ID so obviously you will have to
reduce that quantity for the product
that we are selling how do we know which
is the product that we are selling we
are passing in the product ID into the
stored procedure and that's what we are
using here in the where clause and the
next important thing to come so we have
done half the work we have updated the
stabile product table the next thing is
to insert a row into this TBL product
sales table and obviously to insert a
row into this table we need values for
three columns product ID quantity soul
and product sales i'li product ID and
quantity sold are coming into the
procedure as parameters so there is no
problem in retrieving those values but
product sales
ID is the primary key column and it is
not an identity column so we will have
to supply a value for that manually and
to do that first we have to figure out
what is the maximum value that is
present because if you try to insert
another two into this table you will get
an error you will get a primary key
violation error so we first need to
calculate the max of the product sales
ID and then to that we need to add one
and that's what we are doing here if you
look at the code that we have here it's
slightly complex than what we have just
discussed we are saying okay select at
max of paraxial CD now the reason why we
are using case statement is here is
because of a situation where if there
are no rows at all in TBL products sales
table if this table is empty max of
product sales ID will return now so when
max of
society is returning now when Max of
product sales idea is null then we want
to return zero instead of now because
four and null if you add one it will
still be now that's why when Max of
Product ID products sales ID is now then
return zero else return max of products
society and then to that add one that
way we are computing the value for the
primary key column and then finally
insert those values into TBL product
sales table insert into TBL products
sales values the values for the three
columns that's it
so let's create this procedure now as we
look at one more thing all these
statements updating TBL product table
inserting into TBL product sales table
both of these statements are wrapped
inside begin transaction commit
transaction statements which means our
intention is to basically treat them as
a single unit of work either both of
them should succeed or even if one of
them fails then we want to fail the
other one and rollback the transaction
that's our intention but then here this
may not work as expected but first let's
see it in working action now if you look
at the data that we have in TBL product
table there are 50 desktops and 70
laptops available and let's say we want
to sell you know 10 desktops and do that
to this stored procedure SP cell product
we need to pass in values for the two
parameters product ID the product ID is
two and the quantity that I want to sell
is 10 so let's execute this true
procedure and see what's going to happen
so one row affected now let us see so as
expected desktops quantity available is
40 and there is in row inserted into TBL
product sales now let's try to manually
insert an error into the stored
procedure and the way we can do it is
simply comment this line so the place
where we are actually incrementing the
primary key value by one we are
commenting that line so obviously what's
going to happen now we will get a
primary key
violation so let's try to sell 10 more
desktops and see what happens so before
we do that let's look at okay there are
40 desktops available so I can happily
sell 10 desktops so let's see what
happens when we press a 5 oh we haven't
altered the stored procedure that's why
it sold those 10 now we only changed it
here but we haven't really altered the
procedure so let's alter the procedure
so alter + f5 ok so there are 30
desktops available now let's sell 10
more and see what's going to happen now
we should get that primal key violation
look at this it says one row affected
which means from the 30 desktops that
are available it directed 10 but it
should but it didn't insert a row into
TBL product sales table so which is like
inconsistent so - to detect the errors
and then to rollback the transaction in
the previous session we have seen how to
use edit error system function in this
session we will see how to do the same
thing using try-catch block which is
much easier compared with using edit
error so if you look at this it's
exactly the same procedure all we are
doing here is you know the update TBL
product statement and calculating of the
primary key value and inserting into TBL
product sales all this code is now
wrapped inside a begin try and then try
block so when these statements are being
executed if at all if there is any error
situation what's going to happen the
control will immediately jump to the
catch block and then if you look at in
the catch block what we are saying in
the first the first statement is to
rollback the transaction so we are
ruling the transaction bag so which
means even if this statement has
executed successfully that will also be
rolled back
okay and then data there is some
functions here which we will talk about
in a bit and on the other hand let's say
there are no errors everything is
working fine as expected let's say there
are no errors then what's going to
happen
we are finally committing the
transaction which means that changes
will be made permanent in the database
so it's so easy to use the try-catch
blocks to detect and handle errors
okay so let's alter this procedure
command completed successfully so we
have successfully altered the stored
procedure and another thing to keep in
mind is these functions now look at this
if there is an exception within the
statements here that you can say that
are wrapped inside we can try and then
try if at all there is an exception here
what happens the control immediately
jumps back to the cache block the first
statement in the cache block you know
gets executed and the rest of them so
whenever there is an error condition we
want to find out what is the error
number error message the procedure where
the error has occurred the state of the
error the severity number and the line
number and to do that we have several
built-in sequel server functions which
gives information about those errors and
the names are very meaningful error
number returns the number of the error
error message returns the message of the
error and so forth and so on
okay now remember these functions will
have that error information only within
the context of the catch block if you
try to execute them outside the context
of the catch block they return now let's
say let's select them let's execute them
in isolation I am just selecting them
and executing look at this when I
execute them everything is now okay so
only when there is an error and when the
statement is being executed within the
context of that crash block will we get
the error information from those
functions
okay so these are those functions and
this is the basic syntax for the
try-catch blocks you know any set of
secret statement that you that are
wrapped between begin try and end try
you know whenever those statements are
executed if at all if there is an in any
exception the control will immediately
go to the cache block on the other hand
if there is no exception the control
will go to the statement after the catch
block and remember errors that are
trapped by cache blocks are not returned
to the calling application so if any
part of the error information must be
returned to the application the code in
the cache block must do so by using
Ray's error function in the scope of the
catch block there are several system
functions these are the functions that I
used to retrieve more information about
the error that has occurred these
functions return null if they are
executed outside the scope of the cache
block and we have seen that and
try-catch block cannot be used in a
user-defined function alright so now
let's quickly execute the stored
procedure and see what's going to happen
so let's see what's there at the moment
so we at the moment we have 70 laptops
so let's go ahead and sell 10 laptops so
I'm selling 10 laptops so at the moment
everything should work fine because if
you look at the stood procedure you know
we haven't commented this line which
would generate that primary key
violation error so it should work so
let's copy that let's execute the store
procedure so one row affected and you
should say so quantity available is 60
and there are 10 laptops sold so which
is what we expected now let's go ahead
and comment this lines
so that we get a primary key violation
error let's alter this procedure before
executing that now let's try to sell
desktops let's see how many desktops we
have in the inventory we have 20 laptop
20 desktops let's try to sell five of
them so I want to sell five desktops and
now we are expecting a primary key
violation to occur and then the
transaction should be rolled back so
execute look at this now the primary key
violation has occurred but then since we
trap that inside a catch block you know
we don't have that error information
shown in the output and then we are
ruled back the transaction and if you
look at the output you know in the catch
block we are saying select the error
number error message look at this it
clearly sees what is the error message
violation of primary key constraint and
then which is a procedure that costs the
error SP sell product is the name of the
procedure and then error state error
severity and then error line number at
which line in the procedure to the error
occur line number 34 so to find out
which line is that press ctrl-g and then
type in the line number and click OK it
will take you to the line number and
your cursor will be blinking there okay
so if it's if this is not clear and this
is the line which cost that if this is
not clear another way to show the line
numbers is go to tools options and in
text editor select transaxial general
and select line numbers click OK it
should show the line number so if you
look at the line number it is saying
error line is 34 for some reason it's
showing the line number as this one in
fact it should be this one let's execute
this once again and see what's going to
happen
okay let's okay
maybe we have T's that's why we have
these on the top that's why the line
numbers are not correct so let's plus 34
now it should go to that insert
statement okay on this slide you can
find resources for asp.net c-sharp and
sequence of interview questions that's
it for today thank you for listening
have a great day