SQL User Defined Functions | Table Valued Function vs Scalar Valued Function | Intellipaat


Hey guys welcome back to this SQL
tutorial course. In this session we will learn how to work with user-defined
functions. So these are the two types of user-defined functions in SQL. We have
scalar valued functions and table valued functions. We’ll start with the scalar
valued functions so as the name states a scalar valued function always returns a
scalar value so was scalar value could be int, varchar and so on and this is the syntax for a scalar valued function. So we’ll
type create function and then we’ll give the function name and inside the
parentheses we’ll specify the names of the parameters and their data types and
since the function is going to return a value we will type the returns keyword
and specify the type of data which will be returned after that we will type us
begin and then give the function body and then return the final scalar value
finally we’ll use the end keyword to end the function. So let’s go to SQL
server and create a first scalar valued function. So I’m going to create a scalar
valued function which will take one integer value add five more to the
original value and return the updated value. So let me write the syntax for
that, create function and then I’ll give the function name which would be add
five and inside the parentheses I’ll pass the first parameter. So I’ll name
the parameter to be num and this would be of integer type after that I will type
the returns keyword and then the type of data which we’ll be returning is also of
integer type after that I’ll type us begin and since all we have to do is and
since all we have to do is add five more to the original value so I will not
write anything inside this so I’ll directly type the return keyword and
inside this return keyword I will add five more to the parameter value so that
would be the rate num plus five so what we are basically doing over here is the
value which is sent as parameter inside this function
will take it and add five more to that value and then return it and finally
I’ll type the N keyword to end this function so let me hit execute right so
we have successfully created this add five function. Now let me go ahead and
call this function. So to call the function I will type select and then
I’ll have to type DBO dot and then I’d have to give the function name which is
add five now let’s say I will pass in the number ten I’ll select this I’ll hit
execute right so we see that this function works properly so I’ve sent ten
and the result which you’ve got is 15 similarly let me send hundred as the
parameter now I’ll select I’ll hit execute right so the value which has
returned is 105 so this is how we can create a scalar valued function. Then we
have table valued functions. So these table valued functions return a table
instead of a scalar and this is the syntax for a table valued function. So
here the return type is table so after the returns keyword will type in table
because we are supposed to return a table after that we’ll give the keywords
as return and then given the Select statement now you need to keep in mind
that there are no begin and end keywords for the table valued function so let’s
go to SQL server and create a first table valued function. Now I’ll be
creating a function which would help me to individually extract the male
employees and the female employees from the employee table. So before I do that
let me actually have a glance at the employee table first so I will type
select start from employee right so we have this employee table right in front
of us. Now let me go ahead and create the command
so again I will type create function and then I will give the name of the
function which would be select gender now inside the paranthesis I will pass
in the parameter. So I’ll name the parameter to be gender and this would be
of varchar type so us varchar and the length would be 20 after that I’ll give
the returns keyword and since this is a table valued function the return type
would be table so I’ll type Us return and then give in parentheses and inside
the parentheses I will give my select statement
so the select statement would go something like this, select start from
employee where
each gender is equal to at the rate gender so this e_gender is basically the
a gender column from the employee table and this @gender is the parameter
which will be passing inside this function. So let’s say if the user passes
the value male inside this function then we’ll be extracting all of the records
by the gender value is male similarly if the user passes the value female inside
this then we’ll be extracting all the female employees from this table so let
me select this let me it executes right so we have also created this select
gender function so now using this function let me extract all of the male
employees from the employee table so to do that the syntax would be a bit
different select start from and then I’ll type DBO
dot and then give the name of the function which would be select gender
and inside the parentheses I will pass in the value and since I want all of the
male employees I’ll type male over here right so I’m typing select start from
this because this will basically return a table and from the table which is
returned I would want all of the records so let me select this
I’ll hit execute right so we have successfully extracted all of the male
employees from the employee table similarly if I’d have to extract all of
the female employees the syntax would be similar so I’ll type select start from
and then I will type DBO dot select gender and then I will pass in the value
female I will select this, i will hit execute. So again
we have successfully extracted the female employees from the employee table. So this is how we can create table valued functions in SQL. So thanks for
attending this session. Let’s meet in the next class

One Reply to “SQL User Defined Functions | Table Valued Function vs Scalar Valued Function | Intellipaat”

  1. Got a question on SQL user defined functions? Do write it in the comment and you will get a response immediately by our expert. For MS SQL Server training & certification, call us at US: 1800-216-8930 (Toll Free) or India: +917022374614. You can also write us at [email protected]

Leave a Reply

Your email address will not be published. Required fields are marked *