Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,974 courses, including more Business and personalized recommendations.Start Your Free Trial Now
- View Offline
- Comparing triggers, functions, and stored procedures
- Installing and configuring SQL Server
- Creating a stored procedure
- Returning data using data sets
- Creating user-defined functions
- Using "after," "instead," and nested triggers
- Modifying existing stored procedures
- Implementing logging on DELETE
- Choosing between T-SQL and CLR
- Executing a stored procedure
- Passing parameters
Skill Level Advanced
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.