Stored procedure output parameters or return values Part 20 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to presume technologies I
am venket this is part 20 of sequel
server in this session we'll understand
what stored procedure return values are
the difference between stored procedure
return values and output parameters and
finally when to use output parameters
over written values in part 18 of this
video series we have seen the basics of
stored procedures and in part 19 we have
seen how to create stored procedures
with output parameters if you haven't
watched these two parts I would strongly
encourage you to do so before continuing
with the session now let's first
understand what return values are
whenever you execute a stored procedure
it returns an integer status variable
usually a zero indicates success and a
non zero indicates failure let's
understand what we mean by this
practically okay so we are in sequence
of a management studio and in the object
Explorer okay if you open the
programmability folder you should see
stored procedures folder within which I
have caught a stored procedure SP get
name by ID we have created the stored
procedure in the previous session okay
now if I want to execute the stored
procedure I can just right click on that
and select execute stored procedure now
if you look at the name of the stored
procedure SP get name by ID okay we want
to get the name of the employee by
providing his ID so if I give you no ID
of one then I should get Sam back
okay so let's provide the value for ID
which is 1 and when I click OK look at
this the stored procedure gets executed
I get Sam's name back which is the
expected result but look at this we have
a return value as well and that's an
integer and it is 0 here so 0 return
value usually 0 indicates access and
then non 0 indicates failure so from
this we have understood that when we
execute a stored procedure we get an
integer status variable back ok and if
that integer status variable is 0 then
the stored procedure executed
successfully and nonzero indicates a
failure all right with that now let's
try and understand
the difference between these return
values and output parameters in part 19
of this video series we have seen a lot
about output parameters so in this
session we'll talk about the differences
between output parameters and return
values if you look at the procedure that
I have written here this procedure is
called SP get total count of employees
okay so in this table I have got 10
employees so I want to get the total
count of these employees so we are
creating a stored procedure for that as
we get total count of employees and we
have an input parameter at the total
count okay ah sorry an output parameter
at total count so this is a stored
procedure which we are creating with
this output parameter and what are we
doing we are initializing that output
parameter with the count function okay
and let's create that practically okay I
want to get the total count of employees
so what we can do we can create the
stored procedure create procedure SP get
total count okay let's call it get total
count 1 and obviously to get that total
count to receive the total count let's
use an output parameter add total count
and this is going to be of type integer
and it's going to be output parameter as
begin and now what we want to do we want
to select the total count of employees
and initialize this output parameter
with that count
so select add count is equal to count
off
ID from TBL employee table okay so let's
execute the stored procedure I mean
let's create this stored procedure so
when we execute the stored procedure
God's created successfully so we have
created the stored procedure now now
this has stored procedure with output
parameter and if we want to execute the
stored procedure with output parameter
we have to first declare an integer
variable and then pass this integer
variable to the stored procedure
with an output keyword you have to tell
this procedure that this is an output
parameter and then we select the value
that is returned back into this output
parameter okay so let's do that
declare let's call ed to tow and integer
so we want to execute the Stuart
procedure so which is nothing but SP get
total count and we want to pass this
output pair I mean this variable here
and use the keyword out so that this
variable will receive you know the value
and finally what we want to do you can
either print it or select it it's up to
you what you want to do
so let's print it here in this case okay
so when we execute this we should get
number 10 back okay which is the case so
what we have done now we have created
astute procedure with an output
parameter and which will receive the
total number of employees and we have
executed that stored procedure which
will print the total number of employees
ok now let's go ahead and do the same
thing using return values okay so we are
creating this procedure if you look at
this this procedure is using the return
value a create procedure SP get total
count of employees - as begin and if you
look at this we have a return keyword
here now and there is a bracket I mean
there are pair of brackets here and
within that brackets we are saying
select count of ID from employees
meaning we are trying to count all the
employees here ok so we get you know ten
employees so we are selecting number ten
and we are returning that ten okay so
this procedure what it's doing it's
returning the total count of employees
and this procedure it does essentially
the same but in a different way using or
parameters okay
so let's create the second procedure so
let's call this SP get total count two
and we are not going to have an output
parameter now instead what we are going
to do we are just going to return
that total count okay but you should
have a bracket here okay so this pair of
brackets ensures this statements get the
statement gets executed first and
whatever is the output that is returned
back using this return keyword so let's
execute this procedure now so we have
that procedure created now if you want
to execute that stored procedure okay
look at what you are doing here you are
creating a variable of type integer and
then when you execute this tote
procedure using this execute keyword it
returns a value back so we are taking
that value and initializing this
variable and finally what we are doing
we are selecting it or you can print it
okay so let's execute as we get total
count two to execute that obviously we
need a variable of type integer execute
this 1sp get total count two and then
what we want to do when this stored
procedure returns a value I want to
initialize this variable and finally
print that so obviously when I execute
this we should get ten okay so now if
you look at this I am able to print the
total number of employees in my
organization you know using output
parameters as well as using return
values okay now let's look at an example
where return status variables cannot be
used but output parameters can be used
okay all right so now let's say I want
you to write a stored procedure which
gives the name of an employee when I
give it an ID so if I pass ID into the
stored procedure it has to give the name
of the employee to me back okay so
obviously your I mean ID is an input
parameter whereas name is an output
parameter okay and what we are doing we
are initializing this name output
parameter with the name of the employee
from employee table where ID is equal to
whatever ID via plus again okay and
again this should be pretty
straightforward
we have spoken extensively about output
parameters in part 19 of this video
series so if you are new to
put parameters please watch that video
first okay
so let's create the stored procedure SP
get name by ID 1
ok so create procedure let's call this
SP get name by ID 1 and this procedure
needs to have 2 parameters you know when
we pass in ID we will get the name back
so we need an integer parameter at ID
integer and we also need an output
parameter which can receive the name of
the employee back
so f name since name is a string it's n
where K of 20 and this is an output
parameter so we need to use the output
keyword as begin and what we want to do
we want to select add name is equal to
name from TBL employee table where ID is
equal to at ID that we are passing in
okay so let's execute the store
procedure okay so we got the stored
procedure created now now let's execute
this to execute the stored procedure
obviously we need a variable to receive
the name of the employee so let's call
this F and this is going to be of type
and we're care of 20 and what we want to
do is execute SP get name by ID okay so
if you want to execute the stored
procedure you have to pass an ID so
let's say we will pass 1 and we want the
name of the person back we know that we
will receive the name into output
parameters so we should have a variable
which will receive that so I have
created a variable for that pass that
here and you have to use the out keyword
otherwise this output
Peter will not receive the value and
finally what we want to do we want to
say maybe name is equal to whatever this
variable has okay so let's execute the
stored procedure so when we execute that
name is Sam okay
so we are able to achieve you know we
are able to retrieve the name of an
employee by passing an ID ID is an input
parameter whereas name is an output
parameter so our stored procedure is
capable of returning the name of an
employee which is of type and we're care
let's try to do the same thing using
output parameters so we have executed
the stored procedure with output
parameter now let's create a stored
procedure which returns the name of the
person given we provide it an ID okay
the same kind of a procedure but using
return values okay so if you look at
this we're creating procedure we are
having an ID parameter input parameter
and look at this what it's doing it's
selecting the name of the employee from
TBL employee table their ID is equal to
at ID and that has to be inside this
bracket so whatever this query brings
back that is being returned back okay
so let's create the stored procedure so
create procedure SP get name by ID let's
call that two and we will have only an
input parameter no output parameter as
begin okay select instead of using the
output parameter what we will do we will
execute this one okay this query gets
evaluated which selects the name of the
employee for the provided ID and we
finally return that back okay so let's
create the stored procedure so we have
that stored procedure created and
obviously if you want to execute that
you know since we are using return
values you create a variable of type and
we're cap and you will say okay that
variable is equal to whatever is the
result of the stored procedure okay so
let's do that here okay so we declared
this variable and what I want to say
is at name is equal to execute that
stored procedure it's not one it's going
to be two because this is the procedure
that we want to execute and let's pass
one as the ID of the employee and let's
execute this and see what happens okay
so look at this conversion fail when
converting the N where care value Sam to
data type integer now if you remember at
the beginning of this video session we
have spoken that whenever you execute a
stored procedure it returns an integer
status variable
okay now this return value here when we
execute this the return value of this
tooth procedure should always be an
integer okay
a value of zero indicates success and
non zero indicates failure okay but what
did we do here we are trying to return
an N where care type do you know you are
able to create the stored procedure
successfully at runtime what's going to
happen you know ID is equal to one we
are passing one here so for one the name
is Sam Sam is the name of the employee
so it tries to convert Sam into an
integer because the return value of his
stored procedure is an integer okay you
cannot convert Sam word into an integer
and it fails and that's why we get this
error so keep in mind return values you
can use return values only to return an
integer okay and that too only one value
let us say I have a scenario where I
want to return the name and gender of an
employee can I do that with return
values no you cannot first of all they
are string datatype so you cannot do
that even if they are integers let's say
for example I want to written maybe the
you know pin code and at the age of a
person both of them let's say they are
integers can I do that with return
values you cannot because a return value
is only one value okay but can I do the
same thing with output parameters
absolutely you can return any number of
values with output parameters and any
data type whereas return values has to
be integer and you can only return one
value okay so those are the differences
between
so if you look at the differences
between return values and output
parameters returns status value only
integer datatype where as output
parameters can be any datatype because
you have the flexibility of specifying
the you know the the datatype for output
parameters look at this when you create
an output parameter you specify the
datatype but you don't have that
flexibility with return values so they
are always integers whereas output
parameters any datatype return value is
only one value they can return only one
value but output parameters you can
return any number of values and usually
in general at least I use return status
value to convey success or failure of a
stored procedure especially when we are
dealing with nester stored procedures
okay and if you really want to return
values like name count age etc we should
be using output parameters not return
status variables values all right on
this slide you can find resources for
asp.net and C sharp interview questions
that's it for today thank you for
listening have a great day