1. Introduction to Scalar-Valued Function
Stored Procedure and function in SQL server are the compiled unit of SQL Program which performs a specific task. Unlike a stored procedure, a User-Defined Function in SQL server can take part in the SQL Select Queries. In this example, we will try to create and use the Scalar-Valued Function in the SQL Server. A Scalar-Valued function returns a single value to its caller.
We create the Examples in this article using Microsoft supplied Titles table of Pubs database. You can download the SQL Script that creates the Pubs and NorthWnd database from the SQL 2005 Section of this site. Before we begin, here is the content of the Titles table that we can refer while going through the article:
2. Scalar-Valued Function Example
Just like a stored procedure, the SQL Server function also performs a specific task and returns a value to the caller. Inside the body of the function, we decide the return value to the caller. After creating the function, we can use it in the same way how one can use the in-build functions of SQL Server.
The below picture shows a set of T-SQL for making a Scalar-Valued function called TotalBooks. This function counts number of titles from the Pubs.Title table and returns it to the caller:
Explanation
Just like a stored procedure, here we use the keyword Create Function to create a Scalar-Valued Function (Marker 1) with a name (Marker 4) which suits the duty of the body. Since the function returns a value to the caller, the keyword returns is used to point out the data type returned by the function. In our Example, the function TotalBooks returns an integer data type (Marked as 2).
In the function’s body, we decide the duties of the function and what it should return to the caller. The body of the function goes in between the Begin & End pair. In our Example, we declare the variable BookCount (Marked as 5) as integer Type. The function body computes the total number of the Title by using a Count aggregate function and it assigns it to the internal member @BookCount (Marked as 6). Then this book count is returned (Marked as 3 and 7) to the caller of the function. Notice the use of keywords Returns (Marked as 2) and Return (Marked as 3) where we usually make mistakes. The Function is ready. Now we can verify it in the SQL Server Management Studio by expanding the Scalar-Valued Function node under the Programmability node of the Database as shown in the below picture:
Executing the Function
While executing the function, the function name should be qualified with the schema name. In our case it is the default DBO schema. Also, do not forget to place the parenthesis after the function name. The below picture shows how one can execute the function:
The below video shows creating and executing the Function which we discussed above.
Video 1: Simple Scalar-Valued Function
3. Function With Parameters
In the previous section, we created a simple Scalar-Valued function. A Scalar-Valued function can take parameters as well. In this section, we will create a function that returns a value which is the total of all titles that belong to a given book section. To do this, the function will expect a Book Section as a parameter from the caller. Have a look at the below example:
Here, we are building a function called TotalPrice which takes a parameter called Type (Marked as 1). This parameter evaluates to NULL when the caller does not supply value to it. Note, this parameter datatype should match with the Type column in the Title table, as we may also supply the parameter value from the table column. Since we pull out the Total Price from the Price column of the Title table, we give the return type as Money (Marked as 1) which is matching with the corresponding table column type. The Null on Null Input states that the function returns directly when one or more null parameter is come across in the parameter list (Marked as 2).
Other part of the function is like what we had written in the previous section. However, you can see how the parameter passed in is used inside the body of the function. The function uses the parameter in the where-clause of the select query which we formed inside the Function body (Marked as 3). Here, our function gets the total price of the book section specified by the parameter and assigns it to the local variable @PriceTotal. We then return this variable to the caller of the function.
4. Supplying Parameter(s)
In the above example, you may think leaving empty in the parameter list will supply the default value of null. But it is not right in case of SQL Server 2005 or later. Have a look at the Query and the Error message below:
|
1 |
Select Dbo.TotalPrice(); |
Error Message:
Msg 313, Level 16, State 2, Line 1
An insufficient number of arguments were supplied for the procedure or function Dbo.TotalPrice.
The correct way of supplying the default parameter is shown below:
|
1 |
Select Dbo.TotalPrice(Default); |
The above query returns null as it finds a null parameter. Look at the example and section marked as 2. Other parameter passing techniques are pointed out through a picture shown below:
In the First highlighted portion of the picture, you can see the function called two times by the caller by supplying the char constants two times (mod_cook and business). The function returns the Total price value for the section’s mod_cook and business.
The second highlighted section shows passing the parameter from the table itself. Here, we send the Type column of the Title table as the parameter to the function.
The third highlighted section shows the result of executing the query. The query returns 6 rows (As we ask for distinct of Type). Hence it invokes the function 6 times and each time it supplies the value of type column from the Title table. The below video explains how to use our Scalar-Valued function in an SQL Select Query.
Video 2: Scalar-Valued function with Parameter
Listing 1: Creating Simple Scalar-Valued Function
|
1 2 3 4 5 6 7 8 9 |
--Simple Function Create Function TotalBooks() returns int as Begin declare @BookCount as int; Select @BookCount = Count(Title_id) from Titles; return @BookCount; end; |
Listing 2: Scalar-Valued Function With Parameter
|
1 2 3 4 5 6 7 8 9 10 11 |
--Function with Parameter Create Function TotalPrice(@Type char(12)= null) returns money with returns null on null input as Begin declare @PriceTotal money; Select @PriceTotal=SUM(Price) From Titles Where Type=@Type; return @PriceTotal; end; |
Note: You can download Pubs and NorthWnd DB Creation SQL from the SQL Server Topic Page.
Categories: SQL Server
Tags: Executing Remove term: Scalar-Valued Function Scalar-Valued Function, Scalar-Valued Function, SQL return vs returns, With Returns
Do you like this Example? Please comment about it for others!!
This site uses Akismet to reduce spam. Learn how your comment data is processed.