Scalar-valued user-defined functions - SQL Server Tutorial

- [Instructor] Developers can write their own functions to extend the capabilities of their SQL Server databases. These are called User Defined Functions or UDF. User defined functions can fall into two categories. Functions that return a single value are called scalar functions, and functions that return rows and columns are called table valued functions. Let's take a look at writing a custom scalar function that returns the square of an input number. Make sure that we're using the wide world importers database either by running this use statement or switching it over here using the drop down menu. And then we'll write a basic function to square a number. Functions are created with a create function statement. Next, we need to name the function and place it inside of a schema. In the wide world importers database, if I expand the tables folder, you'll see that one of our existing schemas is called application. So I'll just save our function inside of the application schema. I'm going to call this function square a number. Next inside of parentheses, we pass in any input parameters that we want the function to process. We do that by using a SQL Server variable. And in SQL Server, variables are named with the app symbol. You can name your input variables, whatever you'd like. I'll call mine input number. Next, we need to specify what type of data this input variable will accept. We do that with the as keyword followed by the data type. In this case, we're going to pass in an integer value as the input value. If you wanted to list multiple parameters as inputs, you would just list them out in a comma separated list. For instance, I could say comma at second variable, and we might say that this is a char datatype. I only need the one variable for this function. So let's go ahead and erase all of that. After the input parameters, you specify what the function is going to return. And you do that with the keyword returns and the data typed the output will be. In the case of this function, this is going to return an integer value. Next, we have the us keyword followed by begin, then we have any programming logic that we want the function to run through, followed by the end keyword and the closing semicolon. So let's focus on lines ten, 11 and 12. This is what we actually want the function to do. Inside of here, you can use any of the programming constructs that SQL Server supports. One thing that we can do is declare new variables inside of the function. So I'll declare a new variable called output and it will be an integer datatype. Then we can set the value of the output variable equal to the input number multiplied by itself. This input number is whatever we pass into the function when we run it. So once we've done this multiplication, and we've set the output variable, this output variable value is what the function is going to return. It's important to note here that on line number 12, we use the return value to specify what value the function is going to output. But up here on line number seven, we say returns with an S. It's really easy to get those two mixed up. So that's our basic function. Let's go ahead and run line six through 14 and that'll create the function in our server. If you wanted to find it in the object explorer window, let's go ahead and minimize the tables folder and go into the programmability folder for the wide world importers database. Then inside of the functions folder, and finally find these scalar valued functions folder. I'll right click on it and choose refresh and there is the function that we just created. Now let's go ahead and use it in a couple of different ways. First, I can use it in a print statement. This will print a message to the messages window down here at the bottom. I'll say application.square number. The name of our function, and I'll use it to process the number five. When I run line number 17 I get the correct message here that the value of five squared is 25. We can also output it to a results grid by using a select statement. So I'll select application.squarenumber, and this time process the number three. In the result set, I'll name the column result. When I run line 19, I get the column result, and the correct answer of three squared which is nine. You can also use the function with data from the database. I'm going to go back into the tables folder, and I need to find a table that just has some integer numbers that I can pass into it and I've chosen to use the sales.orders table. Here we have a column called order ID that is just an integer value. And I'm just going to use these for the numbers that it contains. So I'll select the top 10 order ID, and I'm just going to call this column a number. Then we'll have a second column. And we'll use our application.squared number function applied to the order ID numbers. We'll call this column the number squared. Then we have our front clause that says from sales orders to point to this table. If I run this, we'll see what the result looks like. Here we have the original numbers that are coming out of the sales.orders table. And here we have the result of that value squared. So one squared is one, 16 squared is 256, 18 squared is 324 and so on. By using the function in this way, we're processing each row separately from every other row. Now whenever you create a function, it's important to test it out for unexpected bugs. When we created this function, we said that we're going to be passing in an integer value. But what happens when we process a number that isn't an integer? For instance, I'm going to select the function and process the number 5.9. When I run this, you might be surprised at the result that we get 25. Because we told SQL server that this function will only accept an integer, it's actually dropping the 0.9 portion of this number and just evaluating the square of five. So if you wanted to accept decimal values, you might have to go back up to the function and redefine it in order to accept that type of input. When you're done playing around with your function, you can remove it from the database by running a simple drop function and then the name of the function. Let's go ahead and run this to remove that function from the system. So that's how you create a basic function inside of SQL Server. You say create function, you name the function, you define any input parameters, then you say what it returns. Finally, you have us begin any programming logic and then the key word end.