Choose function in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 121 of signal server
tutorial in this video we'll discuss
choose function in signal server this
function is introduced in sequel server
2012 it returns the item at the
specified index from the list of
available values one important thing to
keep in mind is that the index position
starts at one and not zero we have this
index for this function right here first
we specify the name of the function and
then the index of the item that we want
from the list of available values and
then the list of available values itself
here is an example the list of available
values here is the country list so we
have three countries at the moment and
notice the index is two the item within
this list of available values at index
position two is us so when we execute
this query as expected we get us I have
this exact same query already typed here
so when we execute that notice we get us
now if I specify the index position as
one then we should get the first item
which is India now the index position
starts at one and not zero so what do
you think is going to happen if we
specify the index position as zero it's
going to return now and the maximum
index position for this list of
available values is 3 that is UK is the
item that is present at the last index
position and that is 3 so what do you
think is going to happen if we specify
the index position as 4 again it's going
to return now all right now let's look
at an example of using this choose
function with tabular data will use this
employee's table on the left for the
examples in this demo from here so
within this table we have got three
columns ID name and date of birth now we
want to write a query that's going to
retrieve name date of birth and the
month name from date of birth so if you
look at mark is month of birth is
January so we want to display that
similarly for John the month is December
for Amy it is November so we want to
display the month name also along with
their name and date of birth now we can
achieve this either by using case
statement as choose function
first let's see how to achieve this
using case statement so here is our
employees table within our results that
we want name date of birth and then I'm
going to use case statement I'm also
going to use date part function to
retrieve the month number
so using this date part function I'm
going to tell it to give me month number
and we have to pass the date of birth so
I'm going to specify date of birth
column here so when month number is 1
then what do we want to display for
month January
similarly when month number is 2 then we
want to display February so on and so
forth until month number 12 in which
case we want to display December in the
interest of time I have already typed
the required code so I'm going to copy
this code that we see right here so when
12 then December and I'm going to end
the case statement and give this column
and aleeah's let's call this month so we
have our query right here okay so all we
are doing is you know when the month
number is one written January when month
number is two written February so on and
so forth until you know when month
number is 12 and then we end the case
statement and you know we give that
column an early last month
so when we execute this we get the month
name along with name and date of birth
for every employee but with the case
statement look at the amount of code
that we have to write in order to
achieve this now we can simplify this
using choose function so I'm going to
make a copy of that query and instead of
using case I'm going to use choose
function with the choose function what
is the first parameter the first
parameter is the index of the item that
we want from the list of available
values now what is this date part
function going to do it's going to
return as the month number so that will
be the index of the item that we want
okay and then that second parameter is
going to be the comma-separated list of
month names so I'm going to close this
choose function right there and we are
going to give this column and aleeah's
month okay
and here we are going to specify our
list of available values January
February March so on and so forth until
December in the interest of time I have
already typed the required list of
available values so I'm going to paste
that right there so if you look at this
query it's pretty straightforward so
first one is the index of the item that
we want and the second parameter is the
list of available values okay
so let's execute both of these queries
together and see what result we get look
at that we get exactly the same result
but look at the amount of code that we
have to write when we use choose
function so we have that example right
here thank you for listening and have a
great day