Dynamic sql output parameter Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 148 of sequel server
tutorial in this video we'll discuss how
to use output parameters with dynamic
sequel let's understand this with an
example let's flip to sequence of
management studio we're going to make
use of this employee's table in this
demo I'll have the script available to
create this table in the description of
this video and on my blog so here is
what we want to do we want to retrieve
the total number of employees count by
gender so for example if we set gender
to male then we want male employees
clown similarly if we set it to female
then we want female employees count and
we want to do this using dynamic sequel
and here is a query that can do this
notice we have got two variables at c4
and agenda at the moment they have set
gender variable to male and with less
equal variable we have our dynamic
sequel statement select count of star
from employees where gender column
equals whatever value we have in this
gender variable so at the moment this is
going to written as the male employees
count and then to this system store
procedure SP executes sequel we are
passing our dynamic sequel statement the
parameter list and the value for the
gender parameter so when we execute this
notice we get male employees count and
if we want female employees count we
simply replace this variable with the
value of female when we execute this
notice we get female employees count as
expected at the moment we are not using
output parameters within our dynamic
sequel to be able to use output
parameters we have to make a slight
modification to this query so the first
thing that I'm going to do here is
declare a variable I'm going to call it
at count and this is going to be of type
integer next we need to initialize this
variable with the count so with no
dynamics it'll query select add count
equals count of star from employees
where gender equals add gender and we
also need to pass it within the
parameter list right here so along with
the gender parameter we are also going
to pass our count variable which is of
type integer and this is a node
Amida so let's use the output keyword
and we also need to specify it here and
we also need to use the output keyword
and then finally let's select what we
have in our auto parameter when we
execute this we should get the total
number of female employees count if we
want the male employees count
they simply set this variable at gender
to male and when we execute this notice
we get male employees count and at the
moment they're using an output parameter
with our dynamic cycle query if we want
we can very easily convert their stress
to a procedure to convert this to a
stored procedure we use create procedure
statement and let's call the procedure
SD employees count and this procedure is
going to have two parameters gender and
count so let's move these two lines of
code right here and since these are
parameters we don't need the declare
keyword so gender is an input parameter
count is an output parameter so let's
use the output keyword and then we use
the keyword as they can and within the
body of the procedure we'll have all
these statements since we are passing
gender as a parameter we don't have to
initialize that but the value in the
body of the procedure so we can get rid
of that line this is the stored
procedure body let's format that a bit
and then finally we will end it with end
statement so let's go ahead and execute
this so as the employees count procedure
is created and we know it has got two
parameters gender and count now let's
pass male as the value for gender
parameter and let's set a parameter to
zero so when we execute this we get male
employees count and if we want female
employees count we said the gender
parameter to female and when we exclude
this notice we get female employees
count so the key here is to use this
output Giver now let's see what's going
to happen if we don't use this output
keyword let's remove it and alter our
procedure
let's remove these parameters to be able
to alter the procedure the procedure is
now modified successfully let's quickly
test it again at the moment we are
passing zero as the value for the output
parameter and then we execute this
notice we are getting that same zero
back now instead of zero if we pass
minus one and execute this we get that
same minus one and instead of minus one
if we pass now we get that none of that
so here the output parameter is not
working is expected because we forgot to
use the output keyword so remember for
the output parameter to work you will
have to use the output keyword and here
we have the dynamic sequel example with
both input and output parameters and
always remember the output parameter
will not work as expected if you forget
to use the output keyword thank you for
listening and have a great day