SQL Server Programming Part 10 - Table Valued Functions

welcome to this YSL tutorial in this video we're going to teach you how to use table valued functions in Microsoft sequel server we'll begin this session with a very quick recap of what user defined functions are before we move on to show you how to create the most simple type of table valued function which is referred to as an inline table valued function we'll show you how you can use those in queries that you're subsequently write and how you can also modify those functions should unity once we've dealt with the basic table valued functions we'll move on to the horribly named multi statement table valued functions will show you how you can define an MST VF which is no less horrible acronym how you can then use table variables within its definition to hold the set of results that will finally be returned and then how you can use these MST BFS in your future select statements so let's get started in a previous video we've shown you how you can create user-defined functions which you can then employ in your select statements to calculate new values so in this example we're using a function called FN along date it accepts a single parameter which must be update time and it returns a nice long string of text which is that date formatted in a neat way so when we execute the query we'll see his output now this type of function is referred to as a scalar valued function and this type of function always returns just a single data point each time it's called but in this video we're going to teach you how you can use a type of function which returns an entire table as its output the so-called table valued functions to demonstrate the basics of table valued functions we're going to create a function which returns a list of the films made in a specified year and the basic logic of that could be explained in a simple select segment like this one so we're selecting a number of columns from a certain table and in the where clause you have a single criterion where we're looking for all the films where their year of the film's release date is equal to the year 2001 we've created our user-defined function however we're going to be able to encapsulate this logic in a much neater way so here's how it will look when we've written the function itself we're selecting the same columns from the table returned by this function so it's exactly the same conceptually the set of results will be the same it's just that we've encapsulated all of the logic in this neat easy to reuse package to begin creating our table valued function we need a new query window and I've already added in the first few basic statements which tells the character use the movies database and then go which begins a new batch and that's important because the next statement must be the first one in a batch and that's to create a function exactly the same as though you were creating a scalar valued function if you remember having watched that video previously we don't need to think of a sensible name for the function I'm gonna call mine films in a year and following that we can define which input parameters our function will have now if you think back to how I demonstrated that function being used when it was created function requires a single input which must be some kind of a number so that helps us to work out what we want to do here in the definition of our function in a set of parentheses we're going to define a single parameter I'm going to call it at film a year and I'm gonna declare the datatype as int now that we said what kind of data goes into the function we can say what canidates comes back out and hopefully obviously the return type for a table of how each function must be a table so the statement is returns table now with a simple table valued function like the one we're creating here this is referred to as an in-line function or an inline table valued function have you prefer we don't need to specify anything else whatsoever we don't need to define what columns the table will have more types they will have is all implicit in the select statement we use to return a value so returns table is all we need for this particular kind of function having said that we do need to define what the Select statement itself is so we must do that with with a couple of other key words a returns table as and then we're immediately going to return an answer and the answer will be the Select statement we'll use to populate the results of the function now at this point I'm mostly going to cheat because I still have my simple query open up here which I'm going to copy most of the code from everything but the the explicit value that I'm using to demonstrate this this basic query so I copy that part of code pop back to my function definition and paste it in I'm gonna do a quick bit of indenting wise I won't sleep tonight if that's not at all neatly aligned and then all I need to do is replace the constant value 2000 as I had earlier on with a reference to the parameter that we will be passed in when the function is called so that's going to be at film yeah and there we go that's the definition of our function what we need to do next is execute the code to create it sound so what I'm going to do if I execute the code what we're looking for is commands completed successfully once you've successfully created a table valued function you can find it as part of the database in the same way as a scalar valued function so to do that you'll need to be in the object Explorer and you need to have expanded the database you've created the function in and then you need to look for the programmability folder within there the functions folder and finally within there the table valued functions folder if I expand this I should find the my function is sitting there.if to begin with you don't find the function sitting in the folder where you're excited to be try right-clicking on the folder and choosing to refresh it and that will look data for the contents so you'll be able to see it you can then expand the function itself and look at what parameters you've defined for it including what they thought they've been given but that's just a reassurance that the function you created is sitting there now that you've established the function is where it should be the last test really is to try to use it in a query so fortune we already have one open here in fact which tests our films in year function and we're passing in the year 2000 so if I execute this code I should find I get a list of all the films released in the year 2000 if I have a quick tweak here I stop living here 2001 execute the code again and the results are updated automatically a couple of points worth mentioning you must include the schema the function belongs to so in this case it's DPO dot films in year and but the schema name is important when you're using user-defined functions it's also possible that you don't actually have to type out this this word this phrase you can actually drag there the function name indirectly from your object Explorer in which case you can then open the parentheses and add the parameter as necessary and so on once you've created the function and family works what happens if you decide you want to modify it so for example maybe I'd like to be able to use my function to find films released between two different years so what I can do to modify a function one of two things in fact first of all I could use the script that I used to create the function in the first place I could simply then change the word to create to the word alter and then modify the carriage which returns a different set of results what I could also do if I don't have that code available that's close it down without saving the changes is I can find the function listed in my database but I click on his name and choose to modify it and that will open up a script which is essentially exactly the same code as I wrote to begin with there are a few extra system generated lines of code which in this case aren't necessary so I'm going to delete those and after that essentially everything is exactly as you wrote it so what I'm going to do this time I'm going to create an extra parameter so I'm going to rename the first one as a start here and then add a second parameter which will be at end year and that will also be an int datatype then I'm going to modify the where clause so I'm going to look for where their year of the film's release date is between at start year and at end yeah all I need to do now is execute the code to modify the function itself don't forget to do this simply writing the code out doesn't physically change the way your function works so you must execute the code to actually alter the function I can't tell you how many times I've embarrassingly forgotten to do that so let's go away and give this another try then so we still have our code open which uses our films in year function but it now has an extra parameter which I shouldn't need to provide so I'm gonna write in the year 2000 followed by a comma the year 2002 the squiggly road underlined is simply because the Intelli cents haven't yet refreshed itself it doesn't yet recognize that there are two arguments now cheaper meters for the for this function so I can refresh that in one of two ways I can answer the editor menu and choosing tele sense and then choose the Refresh local cache option or just press control shift + R on the keyboard I'll click the link in the menu as it Sir has it there and eventually the squiggly red underline will disappear if I only execute my carriage I should now find I get a list of films released between the years 2000 and 2002 or whichever two years I decide to pass in each time I call the function so we've seen how to use basic in line table of avidan's but we'd like to move on to next is something a bit more complicated the so-called multi statement table valued functions it's a horrible name but a really useful feature so the idea is that we can create a function which doesn't just return the results of a single select statement we can perform multiple select statements build up an entire table of results from various sources and then return the entire thing as the final step and the example we're going to create for our multi statement table valued function is one which allows you to find all of the people who were born in a specified year so here's our function people in year we're passing in the year 1940 and it's going to return all of the actors and all of the directors who were born in that year it's why don't we execute the code that's what we get and of course we can change the year to any year alike and get a completely different set of results the starting point for defining a multi Statesmen table valued function is very similar to defining an inline table valued function so we have the basic used movies in go statement which means that creates function is the first statement in the next batch we've created a unique name for our function people in year we define a single parameter containing a set of parenthesis called at birth year the date type is int and that brings us to the returns statement now previously for an inline table value function we can simply say returns table but that's not quite good enough for a multi-state sment table valid function what we need to do is declare a table variable and say that our function is going to return the value of a table variable called in this case at T as the energies will never see the name of this variable is fairly unimportant what is called so we get away with something very short and succinct we've made a video on how table variables work previously so it might be with a quick watch of that video together get a feel for how table variables work but all we're going to do at this point is define which columns and death types this table variable will contain so we're gonna have three we're gonna have a person name which b VAR char max because we're not quite sure how big the yeah the field will be we'll have a field called person person DOB for date of birth and that's going to hold a date/time data and finally we'll have a better field called person job which will be varchar' and this will contain either the word actor or the word director so the longest word in that pair is 8 characters long so that's um that's the biggest difference so far with a multi statement table valued function you have to return not just a table generically but a specifically defined table variable the next step is to write the statements which will build the set of results before they're finally returned so we can do that with an as keyword and because we've got several statements involved here we should wrap them up in a begin and end block at that point we can then begin inserting records into our table variable so to do that we can say insert into so into button at T and the values I want to select into this statement into the variable the first time I'm going to come from the director table so I'm going to say select from t be other outta the three values that I want to insert the director name first followed by the director date of birth followed by simply the word director so the literal text rector I want out of where clause to make sure that the records are only inserted where the directors date of birth so breaking about in the year of the bharatas date of birth is equal to the value passed in via V at birth year parameter so at birth year and that's the first set of records that will be inserted into the table variable now fortunately with such a simple example I can get away with a quick bit of copy and paste here because the next thing I'd like to do is insert a set of records from the answer table so with a quick bit of Find and Replace I can find the word director and replace with the word after close all that down and that's all of my information inserted into the table all I need to do now is tell my function to return a value the last statement in any functions definition should be to return a value now because with a multi statement table of valued function you define what the return thing is what the other function returns all you need to say at the very bottom is literally the word return so all we need to do now is execute the code to actually create the function which I'm going to do now and hopefully we'll see commands completed successfully and then all we need to do is test that our function works so we create a new query window I'm gonna say use movies and then I'm going to select everything from dbo dots people in year and I'm going to pass in a single parameter that's in 1945 and execute the code and hopefully as expected we'll get a set of results based on the year that we've passed him just the sake of completeness is worthwhile mentioning that multi statement table valued functions can be found in the same place as the inline versions so find the movies database programmability functions and table valued functions again we might we might find that we need to refresh the folder in order to make the functions appear but then you'll see them pop up and again you can modify these don't delete them in the same way as for standard inline table value functions if you've enjoyed this training video you can find many more online training resources at www.weiu.net

Channel: WiseOwlTutorials

Views: 54,531

Keywords: sql, table valued function, inline, multi statement, table, function, wise owl

Id: nCAEgNxC7nU

Length: 16min 22sec (982 seconds)

Published: Tue Feb 19 2013