Optional parameters in sql server stored procedures Part 68 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this part 68 of signal silver tutorial
in this video we'll discuss optional
parameters in sequence over stored
procedures we'll be using this table TBL
employee for this demo here is the
sequal script to create and populate
this table with some sample data
I'll have the script available on my
blog in case if you need it now I want
to write a stored procedure and the
stored procedure should allow me to
search employees within this table TBL
employee by their name email age and
gender so first let's go and create that
procedure so create proc and then the
name of the stored procedure let's call
it SP search employees and we want to be
able to search by name email age and
gender so let's go ahead and I have four
parameters name is of type and we're
care similarly let's add parameters for
email gender and age email is also of
type and we're care
gender is also often of the same type
but age is of type integer so let's
change the data type to end as begin
and now let's implement the body of the
Stroop senior now we want to select all
the rows and columns from table TBL
employee but we need to apply the where
clause because we want to be able to
filter by the provided name email gender
and age so we're name equals death name
whatever value that is present within
that name at name parameter and gender
equals at gender email equals at email
age equals at age all right so let's go
ahead and create the stop position ah so
we have the Stroop CGS P search
employees created and this procedure
expects these four parameters all these
parameters at the moment are mandatory
look at this if I try to execute this
procedure
without supplying values for any of the
parameters look at what's going to
happen you know we have an error
procedure a function SP search employees
expects parameter F name which was not
supplied so we need to supply values for
all of these parameters so here the
parameters are mandatory so how do we
make them optional to make them optional
all we need to do is specify default
values for these parameters so here I'm
going to specify now as the default
value for name okay but you can actually
specify any default value that would
make sense
you know according to the requirements
of your application so I'm going to
specify a null as the default value for
all of the parameters now let's alter
this execute now look at this when I
execute the store procedure look at this
at the moment I am NOT supplying values
for any of the parameters but look at
that I don't get any data that's fine
we'll fix that in just a bit but at
least we don't get that error you know
that name parameter requires value our
email parameter requires well if we
don't have those errors because these
parameters are optional because we have
specified default values for them so
when you specify default values and then
if you don't supply values for those
parameters and when you execute the
stored procedure at execution time the
default values will be used okay now why
are we not getting data because we don't
have name gender email age you know
where these column values are now and
that's the reason why we don't get the
data but then if you want you know to
return all the rows from the table if we
don't supply values for these parameters
then we need to modify our where clause
so I'm going to include a condition here
where name equals add name or @ name is
now okay similarly
let's go ahead and do add the same
condition for the rest of the parameters
that we have here so gender equals at
gender or at gender parameter itself is
now similarly
we're email equals at email or at email
is now age equals at age or age
parameter is now so basically if one of
these conditions is true that's fine so
let's alter this procedure execute this
now look at this at the moment we are
not supplying values for any of the
parameters but I get all the rows
because we don't want the filter to be
applied if I don't supply values for the
parameters now let's say I want to get
all the male employees then let's
specify the value for this gender
parameter so gender parameter is going
to have a value of male so now if we
execute this we should only get male
employees from the table gender equals
from table TBL employee so look at that
we only get male employees now out of
the male employees I want only you know
the employee who has an age of 29 then
we can pass value for that parameter
where 8 equals 29 and if we execute this
notice that we should only get one
employee okay so here we made the stored
procedure parameters optional by
specifying default values all right okay
so how do you make a stored procedure
parameters optional simply specify
default values and here name email age
and gender we have made them optional
and in the where clause we are checking
the parameter values now so that if we
don't supply a value for these
parameters you know we will get all the
rows from the database table and we have
also tested the stored procedure now
let's actually look at where we can use
you know a stored procedure like this
now I might have a web application you
know where we want to allow the end
users to be able to search employees you
know by name email age in gender so this
form here can actually use that stored
procedure let's quickly design a web
form and then make you
that store procedure so here I have an
asp.net web application here I have from
one dot aspx you know first of all let's
design this waveform so I need a table
and within that I need one two three and
four TDs
okay so let's go ahead and create a
table and let's set font a family to
area so font families area and let's
also set border for this table border is
one pixel solid black color alright and
then within this table we needed Tia
okay look at that the first tiara you
know I want this text search employees
employees to be spanning across all the
four TDs so what I'm gonna do here is
have one TD and set call span to four
and then if you look at this I also want
you know this line here you know under
search employees and to achieve that
what I'm going to do here is for this TD
I'm going to set a style maybe something
like border bottom to one pixel solid
black so that we get that line okay
and then what do we need there we need
text search employees and let's wrap
that inside a bold tag so search
employees and then let's create another
TR and within the second table row we
need four TDs
okay so let's include four TDs so that's
one TD two three and four
okay within the first didi let's include
a boulder tag and then we need this text
name a text box and then this text email
and then text box you know in the
respective TDS that you see here
so first its name and then within the
third TD we want maybe email and in this
TD we need a text box to enter the name
of the employee so let's quickly drag
and drop a text box control and we also
need a text box within this tini
alright now let's make another table row
because we need you know and another for
TD is where we can have age text box
gender and then a drop-down list of
agenda so let's paste that here and then
let's change these names meaningfully so
the name text box let's actually call it
txt name email text box let's call it
txt email and then in the second row we
want age and gender so this is going to
be age this is going to be gender and
maybe let's call this text box txt age
and this one
txt gender all right now actually for
gender we need a drop-down list not at
text box control so let's drag and drop
it drop down list control here
and let's call this drop-down list STD
el gender and let's hard-code the value
so if you look at genders within our
table to build employee we have male and
female
so let's include to list items so a SP
list item text is going to be male and
value is also going to be male similarly
let's have another text item for female
alright but the other thing is apart
from male and female we also need this
first list item which says any gender so
let's add another list item and I want
that to be the first one text here is
going to be any gender which the end
user sees but then a value is going to
be minus one this is this value we will
use to check if the end user has
selected anything within the drop-down
list so minus 1 for any gender all right
so at this point if you look at the
design of our form we should have caught
you know the first one the first year
our second year and third year finally
we want another T away we need a search
button and then another PR to hold the
gridview control to display employees
data ok all right so let's flip it to
the source mode now I want another PR
here let's set the column span to 4 and
within that we need a button control so
let's drag and drop that
and maybe let's call this BTN search and
then the text on that is going to be
search and then we need another TR where
we can place a grid we control to
display employees
okay so let's drag and drop could be
control that should be on the data and
maybe let's call this GV employees
grid-view
employees okay so at this point we have
the design for our web form so when I
click this button that's when we need to
write the code to invoke the stored
procedure and then retrieve the data
bind it to the grid we control so first
of all I'm actually going to write a
private function so private word and
let's call this get data so this is the
function which is going to you know
execute the stored procedure get the
data and then bind it to the tree tree
control alright the first thing here is
to input the required namespaces we need
a system door data we need system dot
data dot sequel client and we need
system dot configuration okay and in a
web config file I already have a
connection string the name of the
connection string is DVCS
and that is pointing to my local
database called sample okay and I mean
you've seen integrated security alright
so first we need to read that connection
string from web config file and to do
the add string that connection string is
equal to configuration manager dot
connection strings off what is the name
of our connection string d bcs database
connection string so I want the
connection string so once we have the
connection string we need to create a
single connection object so I'm going to
wrap the sequel connection object inside
an using statement so that it gets
disposed of automatically I don't have
to close the connection
explicitly so new sequel connection we
need to specify the connection string so
let's pass the connection string alright
now the next step is to create our
sequel command object so sequel command
CMD equals new sequel command so what is
our sequel command here nothing but it's
true procedure and what's the name of
our stored procedure SP search employees
so let's pass that to the sequel command
object and then we also need to specify
the connection object that we are going
to use to execute this command
okay now this command is a stored
procedure so we need to tell that to the
command object so command dot command
type equals command type dot stored
procedure okay the next important step
is to attach the parameters you know to
this command object now if you look at
this form these parameters could be
optional so if there is no value for
name email age gender if if there are no
value selected or if the user hasn't
typed you know anything into these text
boxes then there's no point in attaching
those parameters the Austral procedure
okay
so first of all we need to check if
there is some text in let's say for
example name textbox if the text is not
equal to string dot empty meaning there
is something only then go ahead create a
parameter and then attach that to the
command object so I'm going to create a
sequel parameter and maybe let's call
this name parameter equals new sequel
parameter and then we have to specify
the name of the parameter and then the
value for that so basically I'm going to
use this overloaded version so what is
the name of the parameter name parameter
is at name and the paid is the value
going to come from it's going to from
come from text name text box okay so we
have the parameter here we need to
attach this to the command object so we
have
the command object is CMD dot parameters
dot add we will add our parameter object
ok in a similar fashion we need to you
know attach parameters the rest of the
parameters so let's make a copy of this
one just to speed things up so this one
is for name next is email so if txt
email dot text if it's not equal to
string dot empty then let's call this
maybe email parameter is equal to what
is the name of the parameter
it's called email and where are we going
to get a value from from email text box
and let's add this to the command object
ok and next age so if txt age dot X not
equal to string dot empty then get the
value from that text box name of the
parameter is going to be age and let's
call this each parameter and finally
drop-down list
for gender so if you remember what is
the default value that we have specified
for gender the default value is minus 1
so if the selected value is not minus 1
only then attach gender parameter to the
command object so here we are going to
check the drop-down list a selected
value so DDL gender dot selected value
if it is not equal to minus 1 then you
know the end user has selected a the
male or female in that case create a
gender parameter so the name of the
parameter is gender and where are we
going to get the value from we are going
to get the value from drop-down list so
DDL gender dot selected value should
give us the value that the user has
selected and then finally we need to
pass that to this method
similarly here H parameter needs to be
passed
all right so at this point we have
created our sequel connection object
prepared our command object specified
that the command type is true procedure
and then we have associated all the
parameters with the command object the
next step is to open the connection so
connection dot open and then execute the
command so command dot execute reader
and then whatever this method is going
to return set that as the data source
for our grid B control so givi employees
dot data source equals whatever we are
going to get back from the database and
invoke the data bind method all right so
all of this code is present inside this
private function get data so when the
page is first loaded so if it's not an
initial I mean if it's if it's an
initial get request so if it's not a
postback meaning if it is the initial
get request for this waveform go ahead
and invoke get data function even when I
click the button you know go ahead and
invoke get data function okay so with
all these changes let's go ahead and run
this now we need to optimize this code a
little bit because there's a lot of
repeating code here I look at this there
are all these lines are repeating
themselves we'll fix that in just a bit
but then first let's look at if our
search functionality works as expected
look at this when the waveform loads
first its it displays all the employees
now let's say I want only male employees
I select male click search look at that
I only get male employees if I want
female click search I get female
employees if I don't select any of the
filters search I get all employees ok if
I want male and I want employees whose
ages may be 35 click search look at that
oh don't we have an employee whose age
is 35 let's say we don't have any male
employee whose age is 35 that's the
reason why we don't have any day
so let's see 30 3 click search because
that I get that one employee all right
now let's optimize this code a little
bit more because I don't want this
repeating code here within our get data
function so let's have another function
here maybe which says if you look at
this piece of code you know what are the
varying pieces here the varying pieces
are the name of the parameter the
control which contains the value okay
those are the only two things that are
changing so we can pass those as
parameters and obviously we also need to
have a reference to the command object
so we'll pass the command object name of
the parameter and the control that
contains the value as parameters to a
function and within the function we can
have these two lines so let's quickly
optimize this code so let's actually
write it here maybe let's call it
private word
maybe attach parameters and then we need
to specify the command object so sequel
command maybe let's call it command and
the parameter name is a string so let's
call it parameter name and what else we
need we need the control that contains
the value now here we can't specify
either text box our drop-down list
because you know we have both of the
controls but I won't have only one
parameter so I'm going to use control
here because both text box and drop-down
list inherit from control class so I can
use control parameter and then as an
argument I can either pass it xbox
control or a drop-down this control and
maybe let's call this control alright so
here what we are going to do is look at
this this control can either be a
textbox control or a drop-down list
control so first I'm going to check if
control is text box and
if control is text-box what I'm going to
do I'm going to typecast an to text box
and then retrieve the text property and
check if it is not equal to string dot
empty because only then we should attach
the command object I mean we should
attach the parameter to the command
object so if that's the case then to the
command object first of all we need to
create the parameter so sequel parameter
maybe let's call it parameter equals new
sequel parameter and then we need to
specify the name of the parameter so
where are we going to get the parameter
it's coming in as a parameter so that's
the name of the parameter and where is
the value coming from the value is going
to coming come from the control itself
so if it's a text box type cast you to
text box and retrieve the text
so we have this equal parameter there
the next step is to attach this three
meter with this command object so
command dot parameters dot add let's add
our parameter object okay so this piece
of code you know since we have this
piece of code we can get rid of all
these lines here okay and instead invoke
that function okay what is the function
attach parameters and then obviously we
need to specify the command object which
is nothing but cm day and then the
parameter name so one of the parameters
names is add name and what is the
control that is going to contain the
parameter value txt name that's the text
box okay in a similar fashion we need to
invoke that method for email and the
control that contains the value is txt
email and gender and the control that
contains the value not gender sorry it's
age because gender is a drop-down list
so the Xth alright now we have another
piece of code here left let's get rid of
this as well now for gender the control
is a drop-down list so within our attach
parameters function I'm going to have an
else--if block here and then we are
going to check if control is drop-down
list then what we are going to do we are
going to typecast the control to be of
type drop-down list and then we are
going to check the selected value if
selected value is not equal to minus 1
that means the user has selected either
male or female from the drop-down list
in that case what do we want to do we
want to do the same thing so sequel
parameter the parameter equals 2 where
are we going to get the parameter name
from again that's coming into the
function as a parameter and then the
control here is drop-down list so
typecast are two drop-down lists and the
property is selected value okay and we
are adding the parameter to the
parameters collection of the command
object okay now with this line we can
get rid of this code here okay and then
let's invoke our function attached
parameters command object and then what
is the name of your parameter at gender
and where are you going to get the value
from DDL gender control alright so this
is a bit better because we have got rid
of that repeating code now our function
is much smaller okay all right so let's
go ahead and run this once again and
check if it it continues to work in the
same way so in the wave form first loads
we should get all data but then if I say
male look at that I get only male
employees and 33 age and I get that
alright that's it for today thank you
for listening have a great day