Inline table valued functions in sql server Part 31 Video Lecture - SQL

hello welcome to prism technologies I am
venket this is path 31 of sequel server
in this session we'll learn about
creating and calling in line table value
functions where do we use in line table
valued functions now before continuing
with the session I strongly recommend to
watch path 30 of this video series in
part 30 we have seen how to create and
call scalar user-defined functions in
this part we'll learn about in line
table valued functions we know that a
scalar function returns a scalar value a
single value whereas an inline table
valued function returns a table now
let's look at an example of creating an
inline table valued function now if you
look at this I've got an employee table
which has got ID name date of birth
gender and department ID columns now I
want you to write a function that can
give me the output that you can see on
the right-hand side you know the
function should return employees by
gender for example if I passed your
function gender as male I want only the
male employees to be returned and along
the same lines if I pass the gender as
female and I only want the female
employees to be returned okay and to do
that we use the create function
statement as we have used it to create
scalar function okay so create if you
look at the you know implementation of
the function here it's very simple to
understand so we use the create function
statement so create function function
name and F n underscore employees by
gender is the name of the function
because we are going to return employees
by gender and if you look at this
obviously this function needs a
parameter at gender of type and we're
care whose length is 10 okay since
gender is a string we use in we're care
datatype and then this function returns
table okay so this is the parameter that
we are passing into this function at
gender and this function returns table
if you remember in path 30 you know
where we spoke about scaler user-defined
functions scalar function returned a
scalar data type for example maybe n
where care or integer or date/time you
know a scalar value but we
want this function now to return a list
of employees by gender okay so obviously
we want this function to return a table
so we are specifying table as the return
type from this function using the
returns keyword and then we are using as
keyword and return look at this for an
inline table valued function we are not
using the begin and then block if you
remember for the scalar user-defined
function we had begin and then block the
function body was enclosed within that
block but for an inline table valued
function it's a compile time error to
have you know basically the begin and
end block you cannot have it for an
inline table valued function so
immediately after the asked keyword you
specify the return and within brackets
you specify your select statement okay
so what do we want here we want ID name
date of birth gender and department ID
columns so specify them in your select
list from TBL employees table where
gender equals at gender parameter
whatever the user is going to pass in
okay so we are using that parameter
there and we are enclosing that entire
select statement in parentheses okay
just I mean even if you don't indicate
that we don't get an error but but it is
more readable now after whatever is
present in the in this parenthesis is
completed return the output of the
execution you know of the execution
result of the select statement back to
the calling application so when somebody
calls your function return whatever your
select statement is selecting here okay
so if you notice there are three points
to keep in mind when we compare in line
table valued functions with scaler
user-defined functions now with scalar
functions I mean within line table
valued functions we specified table as
the return type whereas for scalar
functions we specify any of the scalar
data types like integer and we're care
etc okay and for scalar functions you
know here the function body is not
enclosed between begin and end block the
structure of the table this is very
important now
get this you're telling your function
it's going to return a table but what
are the columns that are going to be
present in the day in that table you
know basically the structure of the
table we are not defining that here okay
so the structure of the table will be
determined by the Select statement that
you have
so obviously the table that this
function is going to return is going to
have 1 2 3 4 5 columns because your
Select query has 5 columns within its
Select list ok so the structure of the
table that gets returned is determined
by the Select statement within the
function body ok but later in the next
session we'll be talking about multi
statement table valued function there we
have the luxury of specifying the
structure of the table within the return
after the return keyword we'll talk
about that in the next session ok so
let's look quickly you know creating how
to create this function so if you look
at this I have this TBL employees table
which is called the columns that we have
seen on the slide and to create the
function we use the create function
keyword the name of the function and
then obviously the parameter that we
want to pass along with its data type
returns table specify the ask keyword
return and your select statement within
brackets ok
so let's execute this so when I execute
command completed successfully ok so
where did this function get created if
you look at the context we are executing
this query in the sample database so
once I expand sample database go to
programmability and then two functions
there you should see table valued
functions in part 30 when we created
scalar function that was stored within
this scalar valued functions table but
this is a table valued function and in
line table valued function so that gets
stored in this table valued functions
folder if you can't see that just
refresh it and expand the plus sign you
should see the function that we have
just created which is FN underscore
employees by gender ok
now let us see how to call this function
so obviously to call this function you
know we use the syntax select star from
the function name and then pass whatever
parameters you have to pass to that
function now this function returns a
table so treat as if it is a table okay
now if we want to select from a table we
will say select star from that table
name similarly this is an inline table
valued function just issue a select
statement against this inline table
valued function as if you would issue a
select statement against a table so
select star from that function name okay
so let's see how to execute that so
select star from I want all the male
employees so I'm passing in male and
when we execute this query we only get
the male employees back and along the
same lines if I want female employees I
just pass female and execute the query
so I only get the female employees okay
that's the syntax that we use to execute
them in to call the function now if you
want you can even say use the very laws
you know from the table we can use the
where Clause even with the function you
can use the where clause okay this is an
inline table valued function we get a
table back so treat as if it's a table
and you can just say where name is equal
to maybe Pam I only get Pam's record now
when we execute this all right okay
so where can we actually use this in
line table valued function okay you can
use I mean in line table valued
functions can be used to achieve the
functionality of parameterized views now
what are what is a view and water
parameters views we haven't spoken about
them yet we'll be talking about them in
our later sessions so until then don't
worry about this point but just keep in
mind that in line table valued functions
are an exceptional replacement for
parametrized views which we will talk
about in detail in a later session okay
and another place where you can actually
use this in line table valued function
you know is that you can actually use it
in joins as well
you know we know that this inline table
valued function returns a table so the
table returned by the table valued
function can be used in joints with
other tables okay so if you look at this
when we execute this query select star
from function employees by gender we got
this result back since I am passing in
male as the parameter value I get all
male employees like this that you can
see on the slide now if you look at the
output we have got department ID and
they return the table by this function
okay now what I want you to do is I want
you to join this return table with
departments table so that you can give
me the output that you can see here I
want the name of the employee their
gender and their department name if you
look at the table returned by the
function you don't have department name
in that table
so obviously you will have to join they
return the table with the department's
table okay and if you're new to joins we
have already spoken extensively about
joins in part 12 and 13 of this video
series please check those parts okay now
if you look at the query here it's
pretty simple just like how you join a
table with another table we do exactly
the same thing okay so first of all what
are the columns that we want we want the
name gender and department name so you
specify them in your select list select
name gender department name from you
specify your function name passing it
the parameter that you want here we want
to pass in male because we want the male
employees so function name male
employees and then give it a nullius
just like how you would give an alias to
a table okay e for the function and then
join TBL department D on department ID
with employees or department ID okay
that's about it so you can even use the
table that is returned by an inline
table valued function in a joint let's
see the query so here you have that
select name gender department name from
the function give it an Ali ass join
with TBL
and give it another alley as T on
department ID equals employee dot
department ID so when we execute that we
get the output that we expect on this
slide you can find resources for HBO
tonight and c-sharp interview questions
that's it for today thank you for
listening have a great day