YT YouTube2Text

YouTube2Text → Transcripts → Scalar user defined functions in sql server Part 30

Scalar user defined functions in sql server Part 30 — Transcript

by kudvenkat · 3,105 words · 437 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00hello welcome to preim Technologies I'm
0:02wenet this is part 30 of SQL server in
0:05this session we'll learn about user
0:07defined functions and their types
0:09creating and calling a scaler user
0:11defined function places where we can use
0:14these and finally how to alter and drop
0:16a user defined function from Parts 22 to
0:2029 we have learned how to use many of
0:23the buil-in system functions that are
0:25available in SQL server in this session
0:27we'll turn our attention to creating
0:30user defined functions user defined
0:32functions are also called as
0:35udfs in SQL Server there are three types
0:38of user defined functions scalar
0:40functions inline table valued functions
0:42and multi-statement table valued
0:44functions we will be talking about
0:46inline and multi-statement table valued
0:48functions in a later session in this
0:51session we'll concentrate on how to
0:53create Scala functions now before you
0:56know looking at how to create a Scala
0:58function let's look at how to use one of
1:01the existing system functions now all of
1:03the system functions that we have worked
1:05from Parts 22 to 29 are actually Scala
1:09inbuilt system functions okay for
1:12example we have the square function so
1:15let's try to use that and to use that we
1:17use the select keyword and then Square
1:20the name of the function itself and when
1:22I open this parenthesis look at the
1:24intelligence of SQL Server management
1:26Studio it shows that this function is
1:30expecting a float parameter and it and
1:33it returns a float data type a float
1:37value okay so if we give it a number for
1:40example let us say I pass in three so
1:43this function is going to square that
1:45function and return that the square back
1:48to us in the form of float so if you
1:51understand the functions have the
1:53capability of taking in parameters do
1:57some processing and return some value
1:59values back okay so when we execute this
2:03we get nine now is it mandatory for a
2:06function to have a parameter no we have
2:08also looked at many functions which
2:10doesn't take parameters at all for
2:12example if you take the get date method
2:15which Returns the current system date
2:17time it doesn't take any parameter look
2:19at this this function doesn't expect any
2:22parameter it Returns the current date
2:24and time on that SQL Server instance
2:28okay when we press a five we get the
2:30current date and time okay so a function
2:34can have parameters that's optional but
2:36a function should always return a value
2:39okay so now let's go ahead and see how
2:42to create a user defined Scala function
2:45so what is a scalar function a Scala
2:47function is a function that takes zero
2:50or more parameters and returns a value
2:53okay a function may or may not have
2:55parameters but it should return a value
2:59okay uh since a since a scalar function
3:02returns a single scalar value it's
3:04called a scalar function okay and to
3:07create a scalar function we use the
3:08following syntax create function
3:12function name now to create a St
3:14procedure we will use create procedure
3:16procedure name similarly to create a
3:18table we use create table table name to
3:21create a function create function
3:23function name and then we know that a
3:26function can have parameters So within
3:29the parenthesis is you specify the name
3:31of the parameter and its data type okay
3:34you can have you know up to 1024
3:37parameters okay and then a function will
3:41also return a value so to specify what
3:44is this function going to return we use
3:46the returns keyword and then the data
3:48type okay now if you look at the square
3:51function it returns a float data type
3:54whereas get date function returns a date
3:56time data type now the function that we
3:59are going to to write you know whatever
4:01data type that it's going to return you
4:03specify the data type after the returns
4:06keyword now remember a function can
4:09written almost any of the data types
4:11that we have any of the scalar data
4:13types that we have except text and text
4:16image cursor and date time and time
4:18stamp a function cannot return these
4:21data types that you can see here okay
4:24and then as begin end just like the St
4:28procedure the body of of the function
4:31goes inside this begin and end
4:33statements and finally remember your
4:36function should return back the return
4:38return you know the data type you
4:40specified here okay for example if you
4:42are Computing the age of a person then
4:45it should return an
4:47integer okay so obviously if we have to
4:50write a function to compute the age of
4:52the person now obviously to compute the
4:54age we require to know the date of birth
4:57of a person so we need to pass in dat of
4:59birth as the parameter to that function
5:01and it's going to return an integer back
5:04which is nothing but the number of years
5:06all right so let's see how to create a
5:09user defined function that computes the
5:11age of the
5:12person okay
5:15now let's get rid of these function
5:18calls here now if you look at this I
5:20have written this code already uh just
5:23to save some time in typing if you look
5:25at this I've created a variable called
5:27at doob of type date and there is
5:31another variable of type integer age
5:33which is used to hold the age of the
5:34person and I have initialized the date
5:36of birth to something uh 8th October
5:401982 now how do we compute the age of
5:42the person we have already spoken about
5:44this in part 27 of this video series
5:48okay but let's quickly review it so I'm
5:50using the date diff function and then we
5:52are saying okay compute the difference
5:55in years give me the year part whatever
5:57is the difference in years and then this
5:59is my start date and this is my end date
6:02compute the difference between them in
6:04terms of years okay so that will give us
6:07the ear part now you might be wondering
6:10okay look at this let's comment this out
6:13this is not
6:14required if this is not there what's
6:17going to happen let's try to run this
6:19look at this if I run this I still get
6:22the age okay but then there is slightly
6:24a problem here now
6:28today the date is September 3rd
6:322012 okay so if it is September 2012
6:36September 3rd 2012 now let us say I am
6:41born on
6:44maybe
6:45nov
6:4988th 2011 so if it's November 8th 2011
6:54this is 2012 September and if I execute
6:57this I get ages one year I haven't even
7:00completed one year okay so it just
7:04computes the difference in years without
7:06consideration to the days and months
7:08here that's why we get that you know
7:11when I haven't completed actually one
7:13year it returns one year so obviously we
7:16need this case statement to compute the
7:18age correctly so what we are doing here
7:20is basically okay from whatever is the
7:24difference between the ears you know we
7:26are using this case statement and we are
7:28subtracting one or zero okay when do we
7:31subtract one from the difference in
7:33years whenever the date of birth month
7:37is greater than the current system date
7:39and time or if the months are equal and
7:42if the day of the date of birth is
7:44greater than the day of get date now the
7:47logic is not that important here because
7:49our you know we are here to learn about
7:51scalar functions okay so if this is
7:53confusing please watch part 27 where we
7:56have clearly walked through the logic of
7:58how to calculate the number of I mean
8:00the age of a person all right so finally
8:03I mean this code will give us the exact
8:06age so if I execute that it tells me you
8:08haven't even completed one year okay so
8:11that's why it says zero which correctly
8:13computes that so this is a bit of
8:14transact SQL code that I have now how do
8:17I convert this code into a function okay
8:21now if you look at this I want to kind
8:23of make this code into a function which
8:25takes in date of birth as the input
8:28parameter calculate Ates his age and
8:31returns that back as an integer so
8:33obviously to create a function we use
8:36the create function statement so create
8:38function and then the function name so
8:41let's say I want to create you know
8:43maybe a function
8:45like
8:48calculate H obviously a function you
8:52know this function requires date of
8:55birth as the input parameter so let's
8:57say add DOB and this has to be date
9:01parameter and it should
9:05return back an integer which is nothing
9:08but our age of the person as begin and
9:13finally end okay so if you look at the
9:18syntax here create function function
9:20name parameters returns data type as
9:23begin end and within the begin and end
9:27the function body goes in and finally
9:29you should have a return statement okay
9:32so instead of Select you should say you
9:35want to return the age back whoever
9:37calls this function return the age back
9:39to that calling program okay and we
9:42don't have to have this initialization
9:44here anymore because the end user is
9:47going to pass in the date of birth so
9:49you can get rid of that you don't even
9:52require this declaration of the variable
9:54here because you have the
9:56parameter so that's our function so when
9:59we execute this what happens this
10:01function gets created in the sample
10:03database okay so if I expand the
10:06database if we go to the sample database
10:09programmability and functions and this
10:12is a scalar function why is it called a
10:14scalar function because it returns a
10:15single
10:17value okay so that's why it's a scalar
10:19function we will talk about inline and
10:21multi-statement table valued functions
10:23in the next session and if I expand
10:26Scala functions here scalar valued
10:28functions and if if I refresh this you
10:31should see calculate age function there
10:34that we have written and look at the
10:35name it says dbo do calculate age that's
10:39nothing but dbo stands for database
10:41owner and then dot calculate age is the
10:44name of a function and this function is
10:46actually present in Sample database so
10:49the fully qualified name of this
10:51function is sample. db. calculate age
10:56now remember to invoke a user defined I
10:59mean a Scala user defined function you
11:01need to specify at least the two-part
11:04name now let's you know kind of invoke
11:07this function to invoke this function
11:09just like how we have invoked get date
11:11and power and square functions we do it
11:15the same
11:16way but we need to use a two-part name
11:20okay let's understand that let's say my
11:22date of birth is you know
11:26maybe October 88th
11:301982 now look at this I'm just saying
11:33select calculate age and when I execute
11:36this it says calculate age is not
11:38recognized as a built-in function name
11:40that's because whenever you invoke a
11:42scalar user defined function you need to
11:44specify at least the two-part name
11:47database owner dot the name of the
11:49function here the database owner is dbo
11:53so now when we press F5 we should get
11:56the age back and obviously if you want
11:58to give it a a column name you can use
12:01and a column alas so when I press F5 I
12:04get age 29 now you can also use the
12:07fully qualified name which is nothing
12:09but sample. dbo do calculate
12:17age so even that
12:20works all right so we have seen how to
12:23create that um you know age
12:26function and then we also have seen how
12:28to invoke that function now to invoke a
12:30scal user defined function you must
12:32Supply at least the two-part name owner
12:34name do function name and you can also
12:37invoke it using the complete three-part
12:40name okay now let's look at you know how
12:43to use this user Define function we have
12:46seen you know we have used it you know
12:48with the select statement not only that
12:50if you have I mean if you remember we
12:52have been working with a table called
12:54TBL
12:56employees okay so if you look at this we
12:59have this table TBL employees where we
13:01have got the ID name and date of birth
13:03of the person now I want you to write a
13:06query which will give me the name date
13:08of birth and age of the person and
13:10obviously we have a function and look at
13:12this in the database we are not storing
13:14the age of the person but we have date
13:16of birth So based on the date of birth
13:18we can actually calculate the age of the
13:20person and we have written a scalar
13:21function for that so what we are going
13:24to do here okay we want name date of
13:26birth so name date of birth and then the
13:28age of the person to get that we are
13:30using the age function okay db. Ag and
13:34to that function we are passing in you
13:37know the date of birth column so this
13:39function is going to calculate age for
13:42every person and return that okay so you
13:45can use the Scala user defined Scala
13:48function in the select Clause of a query
13:50not only in the select Clause you can
13:53also use it in the we Clause if you see
13:54the second example here now I want all
13:57the people in all the employees whose
14:00age is greater than 30 is that possible
14:03absolutely just like how you use the
14:05function in the select Clause you can
14:07also use it in the we Clause look at
14:09this I'm selecting name date of birth
14:11and age from TBL employees where I'm
14:16using the function again age of date of
14:19birth greater than 30 and that returns
14:22me the employees whose age is greater
14:24than 30 okay let's quickly see how to
14:26execute these queries so select
14:30I want the
14:32ID name and dbo do
14:38calculate age and we have the date of
14:42birth column we want to give an alas as
14:45AG when we press F5 we should get the ID
14:48name and age of the person and similarly
14:51if you want just people's people whose
14:55age is greater than 30 all you do is you
14:58will say okay
14:59whoever age which is given to us by the
15:03calculate age function is greater than
15:0630 so when we execute that we should
15:08only get the two people whose age is
15:10greater than 30 so it's also possible to
15:13use it in the we Clause okay now if you
15:16look back we have also spoken about St
15:18procedures in the past now whatever we
15:21have achieved using this Scala calculate
15:24age function we can also achieve it
15:27using store procedures we can write sto
15:29procedure which takes in I mean if you
15:32want you can quickly
15:36convert this function by the way if you
15:39want to look at the text of this
15:41function you can use the SP help text
15:44function we have seen you know the
15:46purpose of this function we have used it
15:49with stor procedures as well if you want
15:51to look at the text of a procedure you
15:53can use Spore help text procedure name
15:57similarly if you want to look at the
15:59text of the function Spore help text and
16:02the function name so now if I look at
16:06this look at this this is the function
16:08implementation now instead of saying I
16:11want a function I can say create
16:13procedure I can also convert this to a
16:15procedure create procedure let's call
16:18this SP calculate age and
16:23then we will say this procedure is going
16:26to take at date of birth date and we
16:29wouldn't say it returns as
16:32begin and finally instead of returning
16:36we will just say select age so when I
16:38execute this what happens we have the St
16:40procedure created SP calculate age and
16:43obviously if you want to execute that we
16:45use
16:46execute again if you're new to St
16:49procedures please check our video
16:50tutorial on St procedures that we
16:52covered in the past and if we pass you
16:55know some date of birth to this
16:59it's going to compute the age and select
17:01that for us okay so whatever we have
17:05achieved using a userdefined function we
17:07can also do that with stor procedures
17:09okay but the places where you can use a
17:14function okay you cannot do that with
17:16store procedures for example we have
17:17just seen that a function can be used in
17:20the select and wear Clause but can I use
17:22a store procedure in the in the select
17:24and we Clause no you cannot do that if
17:26you try to do that you will get an error
17:28so select uh maybe
17:32ID from
17:34TBL employees table so what we want we
17:38want the ID
17:40name and if I wanted the age we have
17:44used that function calculate AG to which
17:48we have passed the date of birth
17:51column okay so if we executed this we
17:54got the age without any problem but can
17:56I do that with store procedures instead
17:58of using the function here in the select
18:00list can I use the stor procedure you
18:03cannot okay if you try to do that you
18:07will get an
18:08error because look at this the way you
18:10pass parameters to store procedure I
18:12mean they are there for a different
18:14purpose okay so that's the advantage of
18:17scalar functions and this is one of the
18:19differences between a scalar user
18:21defined function and a St procedure a
18:23Scala user defined function can be used
18:25in the select and wear class whereas a
18:27store procedure cannot be but there are
18:29several other important differences
18:31between scalar defi Scala user defined
18:34functions and store procedures which we
18:36will be talking about in a later session
18:39okay to alter a function we use alter
18:41function statement and similarly to
18:43delete it we use the drop function
18:45statement just like table or I mean
18:48store procedure or any other database
18:50object we use the same statements alter
18:52function function name drop function
18:55function
18:57name on this slide you can find
18:59resources for asp.net and cop interview
19:01questions that's it for today thank you
19:03for listening have a great day

About this transcript

This page contains the full transcript of Scalar user defined functions in sql server Part 30 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 3,105 words across 437 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.