Scalar user defined functions in sql server Part 30 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to pledging technologies I
am venket this is part 30 of sequel
server in this session we'll learn about
user defined functions and their types
creating and calling a scalar
user-defined function places where we
can use these and finally have to alter
and drop a user-defined function from
parts 22 to 29 we have learned how to
use many of the built-in system
functions that are available in sequel
server in this session we'll turn our
attention to creating user-defined
functions user-defined functions are
also called as UDF's in sequel server
there are three types of user-defined
functions scalar functions in line table
valued functions and multi statement
table valued functions we will be
talking about inline and multi statement
table valued functions in a later
session in this session we'll
concentrate on how to create scalar
functions now before you know looking at
how to create a scalar function let's
look at how to use one of the existing
system functions now all of the system
functions that we have worked from parts
22 to 29 are actually scalar in build
system functions okay for example we
have the squire function so let's try to
use that and use that we use the Select
keyword and then Squire the name of the
function itself and when I open this
parenthesis look at the intelligence of
sequel server management studio it shows
that this function is expecting a flute
parameter and it'll and it returns a
flute datatype a flute value okay so if
we give it a number for example let us
say I pass in 3 so this function is
going to square that function and return
that the Squire back to us in the form
of float so if you understand the
functions have the capability of taking
in parameters do some processing and
return some values back okay so when we
execute this we get 9 now is it
mandatory for a function to have a
parameter no we have also looked at many
functions which doesn't take parameters
at all for example if you
take the get date method which returns
the current system date time it doesn't
take any parameter look at this this
function doesn't expect any parameter it
returns the current date and time on
that sequel server instance okay when we
plus a five we get the current date and
time okay so a function can have
parameters that's optional but a
function should always return a value
okay so now let's go ahead and see how
to create a user-defined scalar function
so what is a scalar function a scalar
function is a function that takes zero
or more parameters and returns a value
okay a function may or may not have
parameters but it should return a value
okay since it's since a scalar function
returns a single scalar value it's
called a scalar function okay and to
create a scalar function we use the
following syntax create function
function name now to create a stored
procedure we will use create procedure
procedure name similarly to create a
table we use create table table name to
create a function create function
function name and then we know that a
function can have parameters so within
the parentheses you specify the name of
the parameter and its data type okay you
can have you know up 2,024 parameters
okay and then a function will also
return a value so to specify what is
this function going to return we use the
returns keyword and then the data type
okay now if you look at the Squier
function it returns a float data type
whereas get it function returns a
date/time data type now the function
that we are going to write you know
whatever data type that it's going to
return you specify the data type after
the returns keyword now remember a
function can written almost any of the
data types that we have any of the
scalar data types that we have except
text and text image cursor and data type
and timestamp a function cannot return
these data types that you can see here
okay and then as B
and just like the stored procedure the
body of the function goes inside this
begin and end statements and finally
remember your function should return
back that return you know the data type
you specified here okay for example if
you are computing the age of a person
then it should return an integer okay so
obviously if we have to write a function
to compute the age of the person now
obviously to compute the age we require
to know the date of birth of a person so
we need to pass in date of birth as the
parameter to that function and it's
going to return an integer back which is
nothing but the number of years all
right so let's see how to create a
user-defined function that computes the
age of the person okay now let's get rid
of these function calls here now if you
look at this I have written this code
already just to save some time in typing
if you look at this I've created a
variable called at do be of type date
and there is another variable of type
integer age which is used all the age of
the person and I have initialized the
date of birth to something 8 October
1982 now how do we compute the age of
the person we have already spoken about
this in part 27 of this video series
okay but let's quickly review it so I'm
using the date diff function and then we
are saying okay compute the difference
in years give me the year part whatever
is the difference in years and then this
is my start date and this is my end date
compute the difference between them in
terms of years okay so that will give us
the ear part now you might be wondering
okay look at this let's comment this out
this is not required if this is not
there what's going to happen let's try
to run this look at this if I run this I
still get the age okay but then there is
slightly a problem here now today the
date is September 3rd 2012 okay so if it
is September 2012 September 3rd 2012 now
let us say
I am born on maybe November 8 2011 so if
it's November 8 2011 this is 2012
September and if I execute this I get HS
one year I haven't even completed one
year okay so it just computes the
difference in years without
consideration to the days and months
here that's why we get that you know
when I haven't completed actually one
year it returns one year so obviously we
need this case statement to compute the
H correctly so what we are doing here is
basically okay from whatever is the
difference between the ears you know we
are using this case statement and we are
subtracting 1 R 0 okay when do we
subtract 1 from the difference in years
whenever the date of birth month is
greater than the current system date and
time or if the months are equal and if
the day of the date of birth is greater
than the day of care date now the logic
is not that important here because our
you know we are here to learn about
scalar functions okay so if this is
confusing please watch part 27 where we
have clearly walked through the logic of
how to calculate the number of I mean
the age of a person all right
so finally I mean this code will give us
the exact date so if I execute that it
tells me you haven't even completed one
year okay so that's why it says zeros
which correctly computes that so this is
a bit of transaxial code that i have now
how do I convert this code into a
function okay now if you look at this I
want to kind of make this code into your
function which takes in date of birth as
the input parameter calculates his age
and returns that back as an integer so
obviously to create a function we use
the create function statement so create
function and then the function name so
let's say I want to create you know
maybe a function like calculate
age obviously a function you know this
function requires date of birth as the
input parameter so let's say add DOB and
this has to be date parameter and it
should return back an integer which is
nothing but our age of the person as
begin and finally end okay so if you
look at the syntax here create function
function named parameters returns data
type as begin end and within the
beginning then the function body goes in
and finally you should have a return
statement okay so instead of select you
should say you want to return the age
back whoever calls this function return
the age back to that calling program
okay and we don't have to have this
initialization here anymore because the
end user is going to pass in the date of
birth so you can get rid of that you
don't even require this declaration of
the variable here because you have the
parameter so that's our function so when
we execute this what happens this
function gets created in the sample
database okay so if I expand the
database if we go to the sample database
programmability and functions and this
is a scalar function why is it called a
scalar function because it returns a
single value okay
so that's why it's a scalar function we
will talk about inline and multi segment
table valued functions in the next
session and if I expand scalar functions
here scalar valued functions and if i
refresh this you should see calculate
age function there that we have written
and look at the name it says DB o dot
calculate age that's nothing but DB o
stands for database owner and then dot
calculate age is the name of our
function and this function is actually
present in sample database so the fully
qualified name of this function is
sample the DB o dot calculate age now
remember to invoke a user-defined I mean
a scalar user-defined function
you need to specify at least the
two-part name let's you know kind of
invoke this function to invoke this
function just like how we have invoked
get date and power and Squier functions
we do it the same way but we need to use
a two-part name let's understand that
let's say my date of birth is you know
maybe October 8 1982 now look at this
I'm just saying select calculate age and
when I execute this it says calculate
age is not recognized as a built-in
function name that's because whenever
you invoke a scalar user-defined
function you need to specify at least
the two-part name database owner dot the
name of the function here the database
owner is dbo so now when we + f5 we
should get the H back and obviously if
you want to give it a column name you
can use an a column alias so when I
press f5 I get age 29 now you can also
use the fully qualified name which is
nothing but sample dot DB o dot
calculate age so even that works alright
so we have seen how to create that you
know age function and then we also have
seen how to invoke that function now to
invoke a scalar user-defined function
you must supply at least the two-part
name on a name dot function name and you
can also invoke it using the complete
three path name ok now let's look at you
know how to use this user-defined
function we have seen you know we have
used it you know with the Select
statement not only that if you have I
mean if you remember we have been
working with the table called TBL
employees okay so if you look at this we
have this table TBL employees maybe have
got the ID name and date of birth of the
person now I want you to write a query
which will give me the name date of
birth and the age of the person and
obviously we have a function and look at
this in the database we are not storing
the age of the person but we have date
of birth so based on the date of birth
we can actually calculate the age of the
person and we have written a scalar
function for that so what we are going
to do here okay we want name date of
birth so name date of birth and then the
age of the person to get that we are
using the age function ok DB or dot age
and to that function we are passing in
you know the date of birth column so
this function is going to calculate age
for every person and return that ok so
you can use this scalar user-defined
scalar function in the Select Clause of
a query not only in the Select clause
you can also use it in the where Clause
if you see the second example here now I
want all the people in all the employees
whose age is greater than 30
is that possible absolutely just like
how you use the function in the Select
Clause you can also use it in the where
Clause look at this I am selecting name
date of birth and age from TBL employees
where I'm using the function again age
of date of birth greater than 30 and
that returns me the employees whose age
is greater than 30
okay let's quickly see how to execute
these queries so select I want the ID
name and dbo dot calculate age and we
have the date of birth column we want to
give an alias as H and a plus f5 we
should get the ID name and age of the
person and similarly if you want just
people's people whose age is greater
than 30 all you do is you will say okay
whose ever age which is given to us by
the calculate age function is greater
than 30 so when we execute that we
should only get the two people whose age
is greater than 30 so it's also possible
to use it in the where Clause okay now
if you look back we have also spoken
about stored procedures in the past now
whatever we have achieved using this
scaler calculate age function we can
also achieve it using stored procedures
can write stored procedure which takes
in I mean if you want you can quickly
convert this function by the way if you
want to look at the text of this
function you can use the SP help text
function we have seen you know the
purpose of this function we have used it
with stored procedures as well if you
want to look at the text of a procedure
you can use SP underscore help text
procedure name similarly if you want to
look at the text of the function SPNs Co
help text and the function name so now
if I look at this look at this this is
the function implementation now instead
of saying I want a function I can say
create procedure I can also convert this
to a procedure create procedure let's
call this SP calculate age and then we
will say this procedure is going to take
at date of birth date and we wouldn't
say it returns as begin and finally
instead of returning we will just say
select age so when I execute this what
happens we have the stored procedure
created SP calculate age and obviously
if you want to execute that we use
execute again if you're new to stored
procedures please check out video
tutorial and store procedures that we
covered in the past and if we pass you
know some date of birth to this it's
going to compute the age and select that
for us okay
so whatever we have achieved using a
user-defined function we can also do
that with stored procedures okay but the
places where you can use a function okay
you cannot do that with store procedures
for example we have just seen that a
function can be used in the Select and
where clause but can I use a stored
procedure in the way in the Select and
where clause now you cannot do that if
you try to do that you will get an error
so select maybe ID from TBL employees
table so what we want we want the ID
name and if I wanted the age we have
used that function calculate age to
which we have passed the date of birth
column okay so if we executed this we
got the age without any problem but can
I do that with stored procedures instead
of using the function here in the Select
list can I use the stored procedure
you cannot okay if you try to do that
you will get an error because look at
this the way you pass parameters to
stored procedure I mean they are there
for a different purpose okay so that's
the advantage of scalar functions and
this is one of the differences between a
scalar user-defined function and a
stored procedure a scalar user-defined
function can be used in select and where
class whereas a stored procedure cannot
be but there are several other important
differences between scalar defined
scalar user-defined functions and stored
procedures which we will be talking
about in a later session okay to alter a
function we use alter function statement
and similarly to delete it we use the
drop function statement just like table
law
I mean stored procedure or any other
database object we use the same
statements alter function function name
drop function function name on this
slide you can find resources for asp.net
and C sharp interview questions that's
it for today thank you for listening
have a great day