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

this is part 122 of sequel server
tutorial in this video well discus if
function and sequel server this function
is introduced in sequence over 2012 it
returns one of the two values depending
on whether the boolean expression
evaluates to true or false if is a
shorthand way for writing a case
expression we have the syntax here first
we specify the name of the function and
the first parameter is the boolean
expression if this boolean expression
evaluates to true then the second
parameter that is true value will be
written if this boolean expression
evaluates to false then the third
parameter false value will be returned
let's look at an example I'm going to
first declare a variable let's call it
gender ID and this is going to be of
type integer and I'm going to initialize
that to a value of 1 and then I'm going
to use if function so with the if
function the first parameter is a
boolean expression so our boolean
expression is going to be this if at
gender ID equals 1 so this is a boolean
expression if it evaluates to true then
we want to return male if it evaluates
to false then we want to return female
and let's give this column an earliest
let's call this gender so let's execute
this and see what we get notice the
boolean expression evaluates to true so
it returns the true value the second
parameter which is male if we set it to
a value of 2 then this expression will
evaluate to false so it returns the
false value which is the third parameter
female in this case so we now get female
as expected now let's look at an example
of using this if function with table
data so on the left here we have
employee's table in this table we have
got three columns ID name and gender ID
now based on this table we want to write
a query which is going to retrieve name
of the employee gender ID and then the
gender one will be male and two will be
female there are two ways we can achieve
this we can either use case statement or
a function remember if function is a
shorthand way for writing a case
expression first let's see how to
achieve this using case statement so
here is our employees table within our
result set we want the name of the
employee we want gender ID and then we
are going to use case statement to
retrieve gender so case when gender ID
equals one then we want to return male
else we want to return female and we
want to end our case statement and let's
give this column an earliest let's call
this gender let's execute this query and
see what we get so wherever we have
gender ID 1 we get male and wherever we
have gender ID 2 we get female as
expected so here we are using case
statement to achieve this now we can
replace this case statement with a
function because if function is a
shorthand way for writing a case
expression so what I'm going to do is
instead of using this case statement
let's use the if function so with s
function the first parameter is the
boolean expression and the second
parameter is going to be the value that
we want to return if the boolean
expression evaluates to true and the
third parameter is going to be the value
that we want to return if the boolean
expression evaluates to false let's give
this column an alias let's call this
gender and let's get rid of this end all
right so let's execute this and see what
we get notice we get exactly the same
result and we have you know the case
statement and a function right here the
same queries that we have just discussed
thank you for listening and have a great
day