Error handling in sql server 2000 Part 55 Video Lecture - SQL Server Administration

hello welcome to presume technologies I
am venket this is part 55 of signal
Silva in this session we'll learn about
handling errors in signals over 2000 in
the next session we'll talk about how to
handle errors in sequence over 2005 and
later versions before continuing with
the session I strongly recommend to
watch part 18 of this video series where
we have covered the basics of stored
procedures with the introduction of
try-catch blocks in sequence over 2005
error handling in sequel server similar
to programming languages like C sharp in
Java in sequel server 2000 to handle
errors we use a system function app at
error whereas in sequel server 2005 and
later versions we can make use of try
catch blocks which we will talk about in
the next session sometimes these system
functions that begin with @ at-sign are
called as global variables remember they
are not variables and do not have the
same behavior as variables instead they
are very similar to functions and should
be referred to as functions rather than
variables in this session we'll be
working with these two tables TBL
product and TBL product sales tables TBL
product table is like the inventory
table which holds information about the
products that we sell their unit price
and the quantity available product ID is
the primary key and thus TBL product
sales table is the table which contains
the transactions that we made for
example if I sell a product then it
holds information about what product did
we sell which is nothing but product ID
and what is the quantity which is
quantity sold and products is ID is the
primary key for this table so obviously
when we sell a product there are two
things that needs to happen first it
needs to check the product that we are
trying to sell you know do we have
enough quantity available once we
determine that we need to deduct the
quantity that we are trying to sell from
the quantity available and then the next
step is to insert a row into the stabile
product sales table for example if you
are selling laptops for a tidy laptops
is one so one
and how many we want to sell let's say
10 for example 10 is the quantity so so
obviously when we sell a product there
are two things that's happening one is
updating this table updating the
quantity available for the product that
you are selling and the second step is
to insert a new row into this table TBL
products sales let's look at a stored
procedure that is capable of selling
products now if you look at this store
procedure it seems to be very big but
trust me it's very simple to understand
all this procedure does is update this
TBL product table the quantity available
column and insert a new row into this
table with some processing steps in
between let's look at this now this is
the stored procedure which is basically
selling products obviously you know if a
stool procedure has to you know sell
products it has to have two parameters
the product that we want to sell the
product ID that we want to sell and the
quantity that we want to sell both of
them of type integer as we look at this
the first thing the slow procedure is
doing check the stock available for the
product that we want to sell now let us
say for example I want to sell laptops
10 if you look at the product ID is 1 1
is nothing but laptops and what's the
quantity that we want to sell we want to
sell 10 of them so the first thing when
we want to sell 10 laptops the first
thing to do is to check do we have
enough laptops in this talk and to do
that we are declaring a variable of type
integer stock available and into that
variable we are selecting the quantity
available from TBL product where product
ID is equal to 1 1 is nothing but
laptops so this variable will now
contain the stock available for laptops
which in this case is 19 so stock
available will contain 90 now and the
amount that we want to sell is 10 so if
stock available is less than the
quantity that we want to sell stock
available is 90 the quantity that we
want to sell less than 90 less than
and falls so it doesn't come into this
block but on the other hand let us say I
want to sell 100 laptops but then what
is the quantity available only 90 is the
quantity available so what happens it
comes into this F block and it is
throwing an error here
not enough stock available and to threw
that error we are using this function
called raise error in c-sharp we used
through keyword to through an exception
whereas in sequel server if you want to
throw a custom error then we use this
raised error function and if you look at
the raised error function itself it has
got three parameters there are message
that we want to return back okay which
in our case is not enough stock
available and then there is an there are
another two parameters error severity
level and error state now the severity
level in most cases is 16 and 16
indicates general errors that can be
corrected by the user okay so most of
the time you know if you want to return
customers the severity level is 16 which
indicates that these are the errors
which can be corrected by user for
example how do we correct this error now
we know that the stock available is 90
so obviously we cannot tell we cannot
sell hundred laptops so just by reducing
that to 80 probably will fix my error
okay so severity level is 16 here and
the other parameter is this state now
error state is a number between 1 and 21
and 255
but razor of any use when we used razor
or to throw a custom exception custom
error the error state can only be a
value between 1 and 127 but in general
we use 1 so thus the error State is 1
here so this will throw an exception if
the stock available is less than the
quantity that we want to sell on the
other hand if it's not then it comes to
the else part so we have determined when
we when we are in this else part we know
that ok the there is talk available so
if there is enough stock available what
we need to do we need to first update
this TBL product table the quantity now
let's say I want
to sell only 10 laptops okay so let's
say 10 is the quantity that I want to
sell product ID is 1 which is laptops so
we are in the L Spock the first thing
that we have to do is update that
product table quantity available column
so quantity available is equal to
quantity available minus the quantity
that we want to sell in this case we
want to sell 10 laptops so what happens
from 90 we are subtracting 10 and
updating this quantity available column
for laptops row so that's what this
update does and then the next thing is
after we update the stable product table
we need to insert a new row into this
table now to insert a new row into TBL
product sales table you need values for
3 columns okay
products clsid obviously that's the
primary key so you'll have to supply
that the product ID and quantity sold
the product ID and quantity sold are
coming in as parameters to this
procedure so we have those product ID
and quantity cell sold in a 1 and 10 but
the third thing is the primary key
itself we need to calculate the primary
key for this table that we need to
insert now already if you look at the
key values we have 1 & 2 so we need to
calculate you know the next primary key
which is 3 and to do that all we are
doing is we are using an aggregate
function max which returns the product
not the max of the product sales ID and
then look at this what we are doing here
basically is if there are new rows let's
say for example this table is empty we
are making our first transaction for
example okay when this table is empty
when you say you know select max of
product sales ID that will be now so
when that is null your max product sales
ID will also be now when it is null
instead of saying instead of storing
null lenders we are storing 0 else
whatever is the value and the reason why
we are doing this is because look at the
next step what we are doing we are
actually incrementing
by one so if there are rows okay one and
two there are two rows here what happens
it checks here it uses this case didn't
when max of products sales ID is null no
it's not now if it is not now then it
comes here max of products sales ID is
two so our max of products sales head it
contains two and then we are
incrementing that by one so it will
become 3 here and the product ID is one
quantity to sell it is 10 but on the
other hand let's say when there when
there are no rows in this table in that
case what happens
max product max of products sales ID
will be null if that is null then we are
storing 0 in this Max product sales ID
so 0 plus 1 is 1 so the first row will
have a one of you know one for this
column so that's why we are using this
case statement and then finally what we
are doing we are inserting into this
table it's pretty simple all you are
doing I mean there are two three steps
here first check the stock available if
the stock available is less than the
quantity that we want to sell through an
exception otherwise update the product
table with the quantity that we are
selling and then compute the next
primary key that we want to insert into
TBL product sales table and insert a row
into that table that's about it okay
so let's create the store procedure now
so command completed successfully the
stored procedure is now created let's
see what we have in these two tables now
so if you look at this you know the
quantity available there are 100 laptops
and there are 50 desktops laptops
product IDs 1 desktops product ID is 2
now let us say I want to sell you know
product with ID is equal to 110 is the
quantity so when we press this what
should happen this quantity available
for laptop should become 90 and there
should be a new row into this table TBL
products sales table so execute that so
command completed successfully execute
this look at that
very good so 10 decreased from the
quantity available there is a new row
here in TBL products sales with product
ID is equal to 1 quantity sold is equal
to 10 which is as expected now let's try
to put introduce some error into the
store procedure now intentionally I am
getting rid of this line I'm actually
commenting this line so when we comment
this line look at what's going to happen
it gets the maximum of product sales ID
and then it tries to insert the same
product sales ID which is the primary
key in TBL products sales table so
obviously we will get a primary key
violation so at the moment if you look
at the rules that we have in TBL product
and TBL products sales tables the
products sales ID is 1 and what happens
when we try to insert another row with
the same ID we get a primary key
violation error and let's see what's
going to happen now so let's alter the
store procedure
okay so now let's try to sell you know
ten laptops again now remember what
happens when we try to execute the store
procedure it updates the table in the
sense it reduces the inventory from 90
to 80 but then it will not insert a new
row into this table let's see that so if
you look at the available stock now look
at this what's the available stock for
laptops 90 okay now when we sell 10
laptops now what should happen there
should be another row with product ID 1
and quantity sold at 10 and then this 90
should become 80 but then remember we
have intentionally introduced an error
here we are not calculating the product
you know the next primary key value
because of which we get a primary key
violation which means this statement
will be successful and this statement
will not be successful and this is
inside a transaction so what happens
this changes will be made and this
changes will not be made so these
statements are no longer being treated
as a transaction so your our data is now
going to become inconsistent because one
statement fail I mean succeeded and the
other statement fail but still those
changes are retained okay so let's see
what happens first execute look at this
one row affected and then there is a
violation of primary key constraint and
the statement has been terminated and if
you look at this the product the
quantity available see you have reduced
you know the quantity available but you
haven't recorded a sales transaction
which means your update didn't work as
expected okay so this update and insert
should be treated as a single
transaction either both of them should
succeed or none of them should work if
one of them fails the other statement
should also be rolled back okay and why
were we not able to properly do this
transaction because we didn't check for
errors
so in sequel server how can we check for
errors as I told you we use this system
function at at error let us see how to
use
that error system function and then you
know roll back the transaction if there
are errors and if there are no errors
then we will commit the transaction but
then if you look at the way the stored
procedure is written now it's committing
the transaction
you know without even checking for any
errors okay so it's saying update this
calculate the Max product max product
sales ID and then insert the row and
finally commit the transaction okay but
then we'll have to check okay when I
executed this did we encounter any
errors when I executed this did I
encounter any errors when I executed
this did I encounter any errors when
there are you know when there is an
error whenever you execute this
statement of this statement then you
know you will have to roll back the
entire transaction if there are no
errors then we commit the transaction so
let us see how to do that using this at
add error system function so to do that
what we are basically going to do is I'm
going to check if that error is not
equal to zero so basically this system
function you know will contain a non
zero if there is an error if there is no
error if the statement has successfully
executed then it will contain zero so if
edit error is not equal to zero then
that means there was some error okay and
remember if there is an error we want to
roll back this entire transactions again
we haven't spoken about transactions yet
we'll be talking about transactions in a
great detail in the later sessions of
this video series so if there are errors
we want to say roll back transaction you
can either use the complete word or you
can just say roll back Tran just like
how we have done here a begin Tran
commit Tran on the other hand
if it's not equal to if it's equal to
zero then we know there are no errors
and we can commit the transaction so all
I'm doing is a simple modification to
this and and look at this one this line
is still commented which means we are
going to get that primary key violation
but then since we are checking here for
the error condition and then if there
are errors we are saying roll back the
transaction okay so let's say let's
alter this procedure once again so now
let's go ahead and try to sell 10 of
laptops again now before we execute that
let's look at the state of the database
so laptops quantity available is 10 and
we are trying to sell 10 of them so what
should happen they should become 70 and
a row should be inserted here but we
know when it tries to insert a row into
TBL product sales table we'll get an
error when that happens we need to even
roll back the update that was done to
TBL product table let's see if that
happens and just so that we know whether
if a statement is committed or rolled
back what we will do is we will wrap
this inside a begin and end statement
and then we will also print a message
saying that transaction rolled back so
transaction rolled back and along the
same lines will wrap this inside a begin
and end and we'll say transaction
committed so let's alter this
so now let's try to execute the stored
procedure plus F I'll look at this the
statement has been terminated and if you
look at the message transaction rolled
back which means you know we know that
this particular piece of code got
executed transaction rolled back which
means it has undone the update as well
so obviously if you check the quantity
available should still be 80 but on the
other hand if we try to you know
uncomment this line which removes that
error condition there and now what
happens it will it will be able to
successfully execute this insert into a
statement and will be able to commit the
transaction so let's say command
completed successfully now look at the
quantity available 80 and when we sell
10 laptops what should happen this 80
should become 70 and a new row should be
inserted into TBL product sales table so
press f5 transaction committed which
means it has committed the transaction
and if you look at this there's a new
row here and the quantity available is
70 so to check errors we are basically
using this system function at at error
but there is a slight you know thing
that you have to keep in mind when you
use this method error function because
look at this this error that error
returns a nonzero value if there is an
error otherwise 0 indicating that the
previous sequel statement encountered no
errors now keep in mind this netted
error is cleared and reset on each
statement execution now if you have to
get the exact value then you have to
check it immediately following the
statement that's being verified or you
have to save it to a local variable that
can be checked at a later point of time
let's understand what we mean by this
with an example if you look at the
example here look at this let's say TBL
product table if you take TBL product
table here now we already have a row
with ID is equal to 2
and product ID is the primary key so
obviously when we try to insert into
that table another product with the same
primary key what we will get will get an
error primary key violation error and
then here you are checking see look at
this when we execute the sensor
statement we get an error and
immediately after that statement
execution we are checking the value of
this system function at it error if not
equal to zero then print error occurred
otherwise no errors now since we are
checking this system function
immediately after the statement that
cost the error then it retains the error
and it shows error occurred let's see
that okay so it's the same thing here so
let's copy this so obviously when we try
to execute this we'll get a primary key
violation when we execute that look at
that error occurred so it caught in here
but on the other hand let's look at this
statement so what we are doing here we
are inserting into this table the same
query so obviously we will get a primary
key violation error here but then I am
NOT checking the value within that
system function immediately after that
statement execution after that you know
after I execute this statement I have
another statement here select star from
TBL product so now this statement gets
successfully executed when this
statement is successfully executed what
happens is the value that is contained
in this edit error is now reset to zero
because this select statement
successfully executed so at this point
if you check if a trait error or not
equal to zero it will say it will be
equal to 0 why because this statement
successfully executed so the value is
reset so obviously now when I run this
code it will say no errors and that
doesn't make sense right so that's why
anytime you use this edit variable at at
error you will have to check the value
of that immediately look at this
at error
is cleared and reset on each statement
execution check it immediately following
the statement that's being verified or
the other option is save it to a local
variable that can be checked at a later
point of time so that's what has this
one so we are declaring a variable of
type integer at error this is a local
variable and then when you insert you
know when you execute that insert
statement which is gone or through that
primary key violation error and then
immediately after that you are storing
the value that's present in anti-terror
into this local variable and then you
are executing select star from TBL
product which will only reset this
global variable I'm in this global
system function but then this local
variable contains the value that was you
know returned by the set error when this
statement when this insert into
statement was executed so at this point
of time you will get what you expect so
here we are saving the value to a little
local variable at error so obviously now
when we execute this is the same code
that we have seen it should print that
message saying error occurred so error
occurred all right in the next session
we will see how to use try/catch to
handle errors in sequence over 2005 and
2008 on this slide you can find
resources for a speed or nets eShop and
sequel server interview questions that's
it for today thank you for listening
have a great day