Now I'd like to talk about another type of function, what Microsoft calls a Table-valued Function. As the name implies, the results of this function will be an entire table. I've a little code prepared for you in your exercise files, copy that over. So the top starts off just like any other function. We're going to create a function call it 'authors by status' and it will take one input parameter. Line 2 says what we are going to return, and we are going to return a table.
And then lines 3 through 11 define that table. So just like if you were creating a new table, you have to define all the columns, give it a name, a data type and whether or not we allow nulls for each particular field. So it can be a lot of typing to get all this in because we have to define, in a fair amount of detail of the entire table we want to define. The meat of this starts on around line 13, and we see an INSERT statement. So we defined a table. Right now that table is empty.
In order for this to be useful, we have to insert something into that table. And what are we going to insert? We are going to insert the results of a query. Line 16, 17 and 18, define that query. It's everything from the authors table depending on the value in the active field. Remember that the only parameter passed to this function is a value that represents is active. So this query will filter our results based on that value. Go ahead and run this, command(s) completed successfully.
You should see it in there now and there we go. So now this function behaves very much like a table. We can do a SELECT, store. Normally you would say SELECT, store FROM table. But, instead we are going to say select store from our function. Remember, it does take one parameter. So there would be all of our authors whose status is equal to zero, or we can pass it a 1, and there would be all of our authors whose status is equal to one.
This is fairly useful. Obviously, there's other ways that could accomplish this. For example, a view would do some similar things. But, remember you can't pass a parameter to a view. So the strength of Tabled-valued Functions is that we can pass one or more parameters to it and get different results based on those parameters. Other than that they function largely like tables or views.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 74105 Viewers
80 Video lessons · 129523 Viewers
52 Video lessons · 63832 Viewers
59 Video lessons · 49610 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Your file was successfully uploaded.