Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Now I would like to talk a little more about user defined functions, specifically, when Microsoft calls a Scalar-valued Function. Scalar is a single piece of data, such as a single number, a single date, a single piece of text. So any function that returns a single piece of data is called a Scalar-valued Function. These are the most common functions we work with in Microsoft SQL Server. In this example, we will be working with some of the code in your exercise files. The first thing, I'd like to do is populate the authors table with a few addresses.
We're going to be working through some examples with addresses. And right now no one has an address yet, so we'll go ahead and execute some code that should give two our users some addresses. Yes, look at that, now John Doe and Sally Smith both have valid addresses. So now looking at the code that creates the function; the function is called format addresses. It takes four inputs one called city, one called street, one called state and one called zip.
I think those are all self explanatory. On line 7, we see it returns a varchar 255. So it returns a single piece of text that makes it a Scalar-value Function. Lines 12 through 15, you see we are checking to see if any of the inputs were null. If that is the case, if any of them are null, we are just going to return the phrase 'incomplete address'. We don't have enough information to completely format an address. I'll scroll down a little more, we see lines 19 through 23, or in a lightweight case statement, that will take the abbreviation for a state and change it into the full name for the state. I didn't do all 50 lines.
I just did enough for a demo. We see line 26 returns, street space city comma state space zip. So in other words, how an address is typically formatted. Go ahead and run that and we will see the command(s) completed successfully. That's good news. Come back to our sample code, and I have one line that will go ahead and test this function.
We are passing the inputs 100 Main and Buffalo New York and that zip code, and we see the return is pretty much exactly what we expected. Just doing this for ad hoc data is not very interesting. These functions become much more useful when we make them part of a larger query. So here I have a SELECT statement, it's going to select things from the authors table. Select first name, last name and then we see the name of the function dbo.format address.
And then it will pass in the four parameters: address, city, state and zip. When we run that, we see the top two lines returned incomplete address and that is correct. We do not have address information for those two people. And then lines 3 and 4 returned a properly formatted address, they changed the state abbreviation to the full name for the state, and it added in the comma in between the city and state. So that's very useful as being part of the SELECT statement. We can also make a function part of a 'where' clause.
So we can filter this query based on results from that function. To me a very logical thing would be, I want to know everyone that has an incomplete address, and we'll run that. And now we see we've got only the users with the incomplete address, fairly useful. It is also possible to do an order by, on that function field. In this example, this may not be quite as practical as doing the where clause but, we can see that we can order by the value of the functions field.
So any time you create a Scalar- valued Function, it is possible to use that function as part of the SELECT statement, part of the where clause and or part of the order by clause.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91242 Viewers
80 Video lessons · 138207 Viewers
59 Video lessons · 57036 Viewers
52 Video lessons · 70676 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.
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.