Stored procedures with output parameters Part 19 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to presume technologies I
am venket this is part 19 of sequel
server in this session we will learn
creating and executing stored procedures
with output parameters and learn about
some of the useful system stored
procedures now before continuing with
this session I strongly recommend to
watch part 18 of this video series in
part 18 of this video series we have
seen how to create stored procedures
with input parameters in this session we
will see how to create a stored
procedure with output parameter to
create a stored procedure with output
parameter we use the keywords out our
output if you look at the stored
procedure that we have here SP get
employee count by gender okay this
procedure has got two parameters at
gender and at employee count and if you
look at the differences between these
two parameters at employee count has got
output keyword whereas at gender doesn't
have it so this output keyword indicates
that this at employee count is an output
parameter and at gender you know is an
input parameter by default because it
doesn't have the output keyword okay and
if you look at the procedure definition
itself you know all we are doing is we
are you know we are passing in the
gender for example if you pass in male
you know as the gender what this query
is going to do is it's going to count
you know from employee table how many
males are there and then whatever count
you you're getting you're using that
count to initialize this output
parameter okay now let's see how to
actually you know create the stored
procedure so if you look at this we have
this employee table and we have caught
some males and females now we are about
to create a stored procedure which gives
us the employee count by gender and
obviously to create a stored procedure
we use create procedure command so
create procedure and then give them
stored procedure a meaningful name SP
kept
employee count you know by
gender okay we want to get the employee
count by gender and this procedure
obviously if you want the total count of
employees by gender at the minimum you
have to pass the gender information like
do you want the male employee count or
female and employee count so add gender
and this is going to be of type and
we're care let's say 20 is the size and
then we are going to have another
parameter called employee count okay and
this is going to be of type integer and
I want this to be of type output
parameter as begin and so what do we
want to do now we want to select the
total number of employees you know for
the past in gender and to do that we
will say select now if you look at this
select count of star for example from
TBL employee this will give us the total
number of employees in the table but we
don't want total number of employees we
want the total number of you know male
or female employees depending on what
gets passed into the stored procedure so
where gender is equal to now if you if I
say gender is equal to male then it will
give us the total number of male
employees which is seven but we don't
want to be hard coding the gender like
this instead we want to be using the
parameter which the user is going to
pass when he invokes the stored
procedure okay now when we execute this
look at this we get that count and we
are selecting it directly and and you
know we get the result set but we don't
want to do that instead we want to
initialize this output parameter so
that's why we will say select at
employee count is equal to so what's
going to happen whatever we get I mean
whatever this count function returns
instead of selecting it as a result set
we are passing it to the output variable
so the output variable gets I mean
output parameter gets initialized okay
and instead of saying star it's always
good to use the column name there so
let's use ID so that's it so let's
create the stored procedure so we have
successfully created a stored procedure
with an output parameter all right now
to execute the stored procedure with
output parameter now we have created the
stored procedure we want to execute that
okay
now usually we have seen how to execute
a stored procedure with input parameters
in the previous session okay to execute
the stored procedure with output
parameter it's slightly different okay
now you know that this stored procedure
is X is accepting two parameters one is
the gender parameter and the other one
is the employee count gender is an input
parameter employee count is an output
parameter okay
now obviously input parameter means you
have to pass something to the stored
procedure where as output parameter will
return something back to you okay now
when a stool procedure return something
back to you you want to hold it
somewhere obviously in a variable that's
why you first create a variable to
receive the value okay so I'm creating a
variable called @ employee total and
notice the data type of this variable
should match the data type of your
output parameter why because essentially
we get a value of this data type back so
we want a variable you know basically to
hold the value that is going to come out
of the stored procedure so that's why we
are creating a variable of integer
datatype okay and then when we actually
execute the stored procedure look at
this we are executing the stored
procedure here we pass the value for
gender which is nothing but the male and
then what is this query going to do it's
going to count the number of males and
then it's going to initialize you know
this output parameter okay so we are
passing in a variable which is going to
receive that count in this case at
employee total receives the value and
then finally we
that value so let's see how to execute
this okay so obviously to execute the
stored procedure okay first we need to
declare a variable let's say at total
count maybe of type integer so the data
type of this variable should match the
data type of the parameter the output
parameter okay and then what you're
doing is you are executing the stored
procedure which stored procedure is that
SP employee get count by gender and then
when you execute look at this when you
execute that stored procedure obviously
you have to pass in four you know which
gender do you want to count the number
of employees male or female you have to
pass that input value for that input
parameter but whereas for the output
parameter you don't have to pass a value
you will get a value back so here we
want to check the count of male
employees and then we pass this variable
to receive the value back okay now when
you pass that you need to specify the
output keyword otherwise it will be now
okay so that's why to indicate to the
stored procedure okay this at total
count is an output parameter you have to
specify here that you can either say out
or output it doesn't really matter okay
and then finally what you can do is
print the value or you can pass it to
another stored procedure or do anything
you want okay so when we execute this we
should get that value back which is
seven here okay now look at this if I
don't pass the output keyword there and
if I try to execute that look at this it
doesn't print anything because this is
null okay it doesn't receive the value
if you don't specify the output keyword
and you know when you declare it if you
if you haven't initialized the set total
count to anything it will basically be
now and you will end up printing now and
to quickly check that you can check that
here if
you know maybe at total count is now
maybe we want to say print at total
count is now all we are doing is we are
checking if it is null or not else if it
is not now we want to print a message
saying that it is not now print at total
count is not now now if we execute that
look at this we didn't specify the out
keyword if we don't specify the out
keyword this variable will not receive
the value and since we haven't
initialized this variable anywhere else
it is going to stay now and if it is now
it will print this line so let's execute
this so when we execute these lines look
at this at total count is now but on the
other hand if you pass in something you
know if you pass the out keyword what's
going to happen this variable is going
to be initialized with the count of
employees of that gender okay so now
when I execute this you should get the
message saying that at total count is
not now which means it has got some
value all right so keep that in mind
whenever you execute a stored procedure
with output parameter you know for that
output parameter you have to specify the
out keyword otherwise it will not be
initialized and it will always stay now
all right okay so if you don't specify
the output keyword when executing the
stored procedure the the employee total
variable will be null and we have just
seen that okay now here you know we are
passing look at this we are if you look
at the stored procedure the first
parameter is gender parameter and the
second parameter is the employee count
so we are passing in in that order okay
so the first value will be used by this
parameter the second one will be used by
this parameter okay now if you use the
parameter names when you're passing
values to the stored procedure then
the order doesn't really matter for
example look at this let's say I want to
pass my output parameter first how do I
do that
you have to use the parameter names okay
let's get rid of this here okay so
what's the parameter name it's at
employee count so that at employee count
is equal to this output parameter and
you still should have the out keyword
and then you can say at the gender is
equal to I want to pass male okay now
look at this I altered the order in
which we are passing the parameter bus
parameters but since I specify the names
of the parameter the stored procedure
knows which value is for which parameter
okay so obviously if I execute this now
it should work without any issue so we
get seven okay seven male employees all
right there are some extremely useful
system stored procedures which we can
use for a variety of purposes for
example here I have this SP underscore
help procedure it's a system stored
procedure and in the last session we
have learnt that you know any procedure
that starts with SP underscore is
usually a system stored procedure and
user-defined stored procedure shouldn't
be having that name okay so if you see
this SP underscore help system stored
procedure this procedure can be used to
view the information about the stored
procedure like parameter names the data
types etc okay let's see this in action
now if I want to find out more
information about you know the stored
procedure that we have just created I
can use SP underscore he'll take the
stored procedure pasted there and when
we plus you know select those two and
when I press execute you should see okay
name of the stored procedure and look at
this the type is stored procedure this
is a stored procedure and when we have
created it the parameters it has got
their data types etc okay so you can use
SP and s Co help or if you don't want to
use that system stored procedure
you can you know go to programmability
stored procedures folder refresh and you
should see your stored procedure there
and if you expand that you should see
the parameters that it is expecting all
right
okay now this SPN does go help you know
you can use that with any database
object for example tables we use
triggers etc for example look at this
when I use this SP underscore help with
this TBL employee what information do I
get I get all the information about you
know the different columns that are
present in this table there data types
etc any indexes this tables this table
has got any constraints this table has
got basically you get the information
about the object okay
it could be table view stored procedure
trigger etc so SP underscore help work
with any database object and if you
don't want to use the system stored
procedure you can graphically look at
the information about any database
object again or what you can do is
simply highlight that object and press
alt f1 that's a keyboard shortcut and
you you get the exact same information
again okay so that's about SP and ESCO
help system stored procedure and in part
18 of this video series we have seen how
you know what is the purpose of SP
underscore help text if you want to view
the definition of a stored procedure we
use SP underscore help text system
stored procedure okay similarly even if
it is stored procedure with output
parameters you can still use SP
underscore help text system should
procedure ok press f5 you should see the
text of the system stored procedure and
remember in part 18 we have seen how to
encrypt a stored procedure if you have
encrypted a system I mean a stored
procedure you cannot view the text of
that stored procedure ok and finally
this SP underscore depends which is
really useful ok this stored procedure
basically is is used to view the
dependencies of the stored procedure
for example if a stored procedure
depends on a table you know look at this
this stored procedure depends so this
stored procedure where are we getting
the information from the stored
procedure is getting the information
from TBL employee table so this stored
procedure depends on this table okay so
when I execute this so we get that
information okay this stored procedure
is depending on these columns ID and
gender present in these in this table
TBL employee okay now how is this useful
this is useful because let's say I want
to drop TBL employee table okay if I
drop when I say drop I'm going to delete
that table so if I delete TBL employee
table and if I try to execute the stored
procedure what happens it will throw an
error because this stored procedure is
using that table and if that table is
not present obviously we get an error
okay so I can quickly check okay are
there any any dependencies and if there
are dependencies
I wouldn't delete this table okay so
what happens if I delete it instead of
deleting it let's rename it to something
so TBL employee 11 I have just renamed
it to that it's as good as deleting it
and now if we execute the stored
procedure look at what's going to happen
it will throw an error saying invalid
object named TBL employee okay so you
can quickly check okay what are the
dependencies okay so since you want to
delete this table you can check that
table itself so you can use that TBL
employee table here and when you plus a
five look at this there is a stored
procedure that is referring this TBL
employee table okay so if you look at
the messages here in the current
database the specified object which is
nothing but you know TBL employee the
specified object is referenced by the
following which is nothing but the
system stores here so it's saying as
this two systems - I mean this stored
procedure is dependent on this tvl
employee table so
deleting this table may affect the
stored procedure
okay so we'll be cautious when we are
about to do that so SPM score depends
view the dependencies of the stored
procedure this system stored procedure
is very useful especially if you want to
check if there are any stored procedure
that store procedures that are
referencing the table that you are about
to drop SP and the score depends can
also be used with other database objects
like tables etc on this slide you can
find resources phrase without net and
c-sharp interview questions that's it
for today thank you for listening have a
great day