Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
This course investigates several key database-programming concepts: triggers, stored procedures, functions, and .NET CLR (Common Language Runtime) assemblies. Author Martin Guidry shows how to combine these techniques and create a high-quality database using Microsoft SQL Server 2012. The course also covers real-world uses of the INSERT, UPDATE, and DELETE procedures, and how to build a basic web form to connect to your database.
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.
There are currently no FAQs about SQL Server: Triggers, Stored Procedures, and Functions.
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.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.