Working through a Simple Problem

Take a look at the PurchaseOrderHeader table from the AdventureWorks sample database. This table stores the header of sales orders, such as Date, Total, Buyer, and so on. It does not contain the individual items, which are stored in a separate table.

Suppose that you need to calculate the average value of every purchase order made by a given customer. The query might look something like the following code. You can access this sample code from the sample files as \Ch09\Samples01.sql.

 SELECT   ISNULL(AVG(TotalDue), 0) as Amount FROM     Sales.SalesOrderHeader WHERE    (CustomerID = 23) 

This is the result:

The number 23 in the code above is the CustomerID. For other customers, you would supply their CustomerID instead of 23.

Note 

The ISNULL function is used to return an alternate value (in this case, 0) if the specified CustomerID does not exist.

You might want to discover the average purchase of a customer when given the customer name. The Sales.Store table contains a Name column and a CustomerID column. You can obtain the CustomerID for a given name by using the following query.

 SELECT CustomerID FROM Sales.Store WHERE Name = 'Bike World' 

Given the CustomerID, you can use the previous query to find the average purchase amount. The two queries can be combined in the following statement.

 SELECT ISNULL(AVG(Sales.SalesOrderHeader.TotalDue), 0) AS Amount FROM Sales.SalesOrderHeader WHERE CustomerID = (     SELECT CustomerID FROM Sales.Store     WHERE Name = 'Bike World') 

Note that Bike World is a customer name. Notice that the first three lines of this query are similar to those found in the first block of code above. What if you could put the common functionality in a single location? By doing this, you could not only reuse this piece of code, but you could easily change it everywhere it is reused by altering only one location.

Understanding Scalar UDFs

A scalar UDF is similar to what most programming languages call a function. It can accept parameters, execute logic, and return data. The returned data must be a scalar type, that is, a string, number, or date value. It cannot be a table or a cursor. A scalar UDF can be used anywhere that a built-in SQL Server function is allowed, and it must also be deterministic.

A deterministic function cannot call functions that return different values each time one is called, such as some Date/Time functions. Also, it cannot alter the database. On the other hand, a deterministic function may be used with more freedom in a T-SQL query. All T-SQL UDFs must be deterministic or an error will be generated.

The general syntax for defining a scalar UDF is:

 CREATE FUNCTION [owner_name.] function_name    ( [{ @parameter_name  scalar_parameter_type [ = default]} [,..n]]) RETURNS scalar_return_type [WITH <function_option>] [AS] BEGIN    function_body    RETURN scalar_expression END 

In this case, you will define the function as follows on the next page. You can access this sample code from the sample files as \Ch09\Samples02.sql.

 CREATE FUNCTION GetAvgCust(@CustomerID int)    RETURNS money AS BEGIN    DECLARE @Amount MONEY    SET @Amount = 0    SELECT     @Amount = AVG(TotalDue)    FROM         Sales.SalesOrderHeader    WHERE     (CustomerID = @CustomerID)    RETURN ISNULL(@Amount, 0) END 

This function will accept a CustomerID and return the average purchase amount for that customer. You can easily call that function from SQL Server Management Studio, as shown below.

 PRINT dbo.GetAvgCust(23) 

Note 

You can use a UDF as if it were a built-in T-SQL function in places such as queries or expressions.

You can also use the function in more complicated scenarios. To retrieve the average purchase amount when given the customer name, you can use the following query.

 SELECT CustomerID, Name, dbo.GetAvgCust(CustomerID) AS Amount FROM Sales.Store WHERE Name = 'Bike World' 

The UDF can be used repeatedly. For instance, suppose that you want the customer names of those whose purchase orders average more than $100,000. You can write a simple query, such as the following:

 SELECT Name, dbo.GetAvgCust(CustomerID) as Amount FROM Sales.Store WHERE dbo.GetAvgCust(CustomerID) > 120000 

Retrieving Result Sets

In the example presented in the previous section, you reused a query that accepted a parameter and returned a scalar value (a money type, in our example). You may need similar solutions when you want to define something resembling a parameterized view, that is, a view that varies depending on a parameter. Suppose that you want to separate the orders that are larger than a certain value. You can write a query against the Sales.SalesOrderHeader table as shown below. You can access this sample code from the sample files as \Ch09\Samples03.sql.

 SELECT SalesOrderID, CustomerID, TotalDue FROM Sales.SalesOrderHeader WHERE (TotalDue > 170000) 

If you want to use that query over and over again in different scenarios, you might choose to create another type of UDF: an in-line UDF.

Using In-Line UDFs

Think of the in-line UDF as a parameterized SELECT query or a parameterized view. This type of UDF carries some restrictions: because it can only contain a single SELECT statement within it, it therefore cannot be very complicated. It can accept parameters, but cannot change the state of the database. The general format of an in-line UDF is:

 CREATE FUNCTION [owner_name.] function_name    ( [{ @parameter_name  scalar_parameter_type [ = default]} [,..n]]) RETURNS TABLE [WITH <function_option>] [AS]    RETURN select_statement 

Referring back to the example used in the previous section, you can wrap the query inside an in-line UDF with the following code.

 CREATE FUNCTION GetSales(@Amount money) RETURNS TABLE AS    RETURN    SELECT     SalesOrderID, CustomerID, TotalDue    FROM         Sales.SalesOrderHeader    WHERE    (TotalDue > @Amount) 

Once defined, you can use the in-line UDF with parameters as if it were a table, as presented in the following code.

 SELECT * FROM dbo.GetSales(170000) 

The in-line UDF can also be combined with other tables, such as in a JOIN clause.

 SELECT GetSales.SalesOrderID, GetSales.CustomerID,        GetSales.TotalDue, Sales.Store.Name FROM dbo.GetSales(170000) AS GetSales INNER JOIN Sales.Store ON GetSales.CustomerID = Sales.Store.CustomerID 

Using Phantom Tables

In-line UDFs work well if all of the code you wish to write is contained in a single SELECT statement. However, you cannot write complex code inside an in-line UDF, and the return type is limited by the schema of the SELECT query that you use.

Assume that you need a table with the compound interest accrued over a period of time. The number of periods is variable, as are the interest rates. For instance, for twelve periods at 1 percent per period, the table would look like the following figure.

You can generate such a table in many ways. It could be created as an array inside the application. You might instead prefer to create a database table because it is easy to create reports based on database tables or because you need to join the table with other database tables. Using a database table, however, poses some of the following problems.

• You would need to repopulate the table before each use.

• A conflict would be created if your application has several simultaneous users who want to examine different time periods or rates because the table would have to be the same for every user .

You may mitigate the problem by utilizing temporary tables (each user would have her own copy), but you would still need to know when to populate the table and call the specific code.

Using Table-Valued UDFs

A table-valued UDF is a UDF that can return a table. Since you define the tables schema inside the function itself, the table does not need to be based on other database tables.

The interest rate table provides a good example. Just like a scalar UDF, it can contain multiple statements. The following is the general format of a table-valued UDF.

 CREATE FUNCTION [owner_name.] function_name    ( [{ @parameter_name  scalar_parameter_type [ = default]} [,..n]]) RETURNS @table_variable_name TABLE (table_definition) [WITH function_option] AS BEGIN Function_body RETURN END 

The code below returns the interest table for our example. You can access this sample code from the sample files as \Ch09\Samples04.sql.

 CREATE FUNCTION dbo.GetInterest( @NumPeriods int, @PercentInterest money ) RETURNS @InterestTable TABLE    (       Num int,       I money    ) AS BEGIN    DECLARE @N int    SET @N = 0    DECLARE @ITot money    SET @ITot = 1    WHILE @N < @NumPeriods    BEGIN       SET@N=@N+1       SET @ITot = @ITot * (1 + (@PercentInterest / 100))       INSERT INTO @InterestTable VALUES(@N, @ITot)    END    RETURN END 

Tip 

You define the schema of a table-valued UDF inside the function itself after the RETURN clause.

A table-valued UDF is an excellent alternative to a view because it accepts parameters and may contain several complex statements, while a view can only contain a single SQL Server statement.

The table-valued UDF can be used as a table. For instance, to generate a dataset with ten time periods at 6 percent interest per period, you would use:

 SELECT * FROM GetInterest(10, 6) 

Now suppose that you want to determine the future value of your biggest sales (above $200,000) over the next three years using an interest rate of 8 percent per year. The query used to obtain your sales over $200,000 is:

 SELECT * FROM dbo.GetSales(200000) 

You can join this query with another that returns the interest rate.

 SELECT GetSales.SalesOrderID, GetInterest.Num as Year,     GetSales.TotalDue * GetInterest.I AS FutureValue FROM dbo.GetSales(200000) AS GetSales CROSS JOIN dbo.GetInterest(3, 8) AS GetInterest ORDER BY GetSales.SalesOrderID 

Updating Data

You cannot use a UDF to insert data into a table. Although a UDF can query the database since it is by definition deterministic, it cannot change the database. If you want to update the database, you must use a slightly different method: a stored procedure.

Simplifying Procedures

A stored procedure is a small program that runs inside the database server. One reason to write a stored procedure is because some programs are easier to write directly in the database server using T-SQL rather than going back and forth between the application and the server. Consider this simple situation: You have created a database table to store city names and states, as shown below.

The following script is written to create the table. You can access the sample code from the sample files as \Ch09\Samples05.sql.

 CREATE TABLE Cities(    [CITY_ID] [int] IDENTITY(1,1) NOT NULL,    [STATE] [char](2) NOT NULL,    [CITY_NAME] [char](40) NOT NULL,  CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED (   [CITY_ID] ASC )ON [PRIMARY] )ON [PRIMARY] 

You need to develop an insertion routine that performs the following logic: If the City/State pair does not exist, then insert it, obtain the new value of the Identity column (the primary key), and return it. If the City/State pair already exists, then return the primary key value of the existing column in the table.

The following VisualBasic.NET/ADO.NET code is written to accomplish this insertion routine. Do not be concerned if you are not familiar with Visual Basic. The point being illustrated here is that a great deal of work is necessary to accomplish such a simple task. This function is included in the sample files as \Ch09\InsertCityQ.vb.

 Imports Microsoft.SqlServer.Server Imports System.Data.SqlData Function InsertCityQ(ByVal City As String, ByVal State As String) As Integer     Dim RetVal = 0     ' Create and open the connection 

 Dim Cnx As New SqlConnection( _     "Data Source=.;Initial Catalog=AdventureWorks;Integrated Security=True")     Cnx.Open()     Try         ' Check if the city is already in the table         Dim CmdCount As New SqlCommand( _             "SELECT COUNT(*) FROM Cities WHERE " & _             "(STATE = @STATE AND CITY_NAME = @CITY)", Cnx)         CmdCount.Parameters.Add("@STATE", Data.SqlDbType.Char).Value = State         CmdCount.Parameters.Add("@CITY", Data.SqlDbType.Char).Value = City         Dim Count As Integer = CInt(CmdCount.ExecuteScalar())         If Count <= 0 Then             ' It is not, insert into table             Dim CmdInsert As New SqlCommand( _                 "INSERT INTO Cities(STATE, CITY_NAME) VALUES (@STATE, @CITY)", Cnx)             CmdInsert.Parameters.Add("@STATE", Data.SqlDbType.Char).Value = State             CmdInsert.Parameters.Add("@CITY", Data.SqlDbType.Char).Value = City             CmdInsert.ExecuteNonQuery()             ' Retrieve the identity value (primary key)             Dim CmdID As New SqlCommand("SELECT @@identity", Cnx)             RetVal = CInt(CmdID.ExecuteScalar())         Else             ' Yes, it is in the table. Go retrieve the primary key             Dim CmdPK As New SqlCommand("SELECT CITY_ID FROM Cities WHERE " & _                 "(STATE = @STATE AND CITY_NAME = @CITY)", Cnx)             CmdPK.Parameters.Add("@STATE", Data.SqlDbType.Char).Value = State             CmdPK.Parameters.Add("@CITY", Data.SqlDbType.Char).Value = City             Dim DR As SqlDataReader = CmdPK.ExecuteReader()             ' Check if the result set is invalid for some strange reason             If DR Is Nothing Then                 RetVal = -1             Else                 ' Ok, it is valid, now read one line                 If DR.Read() Then                     ' Retrieve the value of column CITY_ID                     Dim nCity_ID As Integer = DR.GetOrdinal("CITY_ID")                     RetVal = DR.GetInt32(nCity_ID)                 Else                     ' Return -1 if the resultset is empty for some strange reason                     RetVal = -1                 End If             End If         End If     Finally         ' Close the connection         Cnx.Close()     End Try     ' Return the primary key value     Return RetVal End Function 

The following script creates a stored procedure that accomplishes the same result.

 Create procedure InsertCity(@STATE char(2), @CITY char(40)) AS BEGIN   DECLARE @RetVal int   SET @RetVal = 0 -- Check if the city is already in the table   IF EXISTS (SELECT * FROM Cities WHERE (STATE = @STATE AND CITY_NAME = @CITY))   BEGIN -- Yes, it is in the table. Go retrieve the primary key     set @RetVal = (SELECT CITY_ID FROM Cities         WHERE (STATE = @STATE AND CITY_NAME = @CITY))   END   ELSE   BEGIN -- It is not, insert into table     INSERT INTO Cities(STATE, CITY_NAME) VALUES (@STATE, @CITY) -- Retrieve the identity value (primary key)     SET @RetVal = @@identity   END   RETURN (@RetVal) END 

The T-SQL code is much simpler to write for several reasons.

• Specific T-SQL functions can be used that are not directly available to Visual Basic programs, such as EXISTS in the example above.

• Using parameters is a simpler process in T-SQL than in Visual Basic.

• The identity value is easier to retrieve because no query is necessary. You simply use @@identity.

• In this example, there is no need to manage a cursor ( SqlDataReader in the Visual Basic code).

Once defined, you can call the stored procedure from any environment able to call stored procedures. Environments include:

• T-SQL code, such as another stored procedure or trigger

• Legacy environments, such as Visual Basic 6.0 and ASP

• Any .NET language (Visual Basic.NET, C#.NET, J#.NET) from any .NET application type (such as ASP.NET or Microsoft ClickOnce)

This example calls the stored procedure from Visual Basic.NET code. This code is included in the sample files as \Ch09\InsertCitySP.vb.

 Imports Microsoft.SqlServer.Server Imports System.Data.SqlData Function InsertCitySP(ByVal City As String, ByVal State As String) As Integer     Dim RetVal = 0     ' Create and open the connection     Dim Cnx As New SqlConnection( _         "Data Source=.;Initial Catalog=AdventureWorks;Integrated Security=True")     Cnx.Open()     Try         ' Call the stored procedure         Dim InsertCity As New SqlCommand("InsertCity", Cnx)         InsertCity.CommandType = Data.CommandType.StoredProcedure         InsertCity.Parameters.Add(New System.Data.SqlClient.SqlParameter( _             "@RETURN_VALUE", System.Data.SqlDbType.Int, 4, _             System.Data.ParameterDirection.ReturnValue, 10, 0, Nothing, _             System.Data.DataRowVersion.Current, False, Nothing, "", "", ""))         InsertCity.Parameters.Add("@STATE", Data.SqlDbType.Char).Value = State         InsertCity.Parameters.Add("@CITY", Data.SqlDbType.Char).Value = City         InsertCity.ExecuteScalar()         RetVal = CInt(InsertCity.Parameters("@RETURN_VALUE").Value)     Finally         ' Close the connection         Cnx.Close()     End Try     ' Return the primary key value     Return RetVal End Function 

You can use stored procedures for almost any database operation. For instance, you could write a stored procedure to update the Cities table when given the primary key and modified city and state values.

 CREATE PROCEDURE UpdateCity (@CITY_ID int, @STATE char(2), @CITY char(40)) AS BEGIN     UPDATE Cities SET STATE = @STATE, CITY_NAME = @CITY WHERE CITY_ID = @CITY_ID END 

You can execute this stored procedure using the following syntax:

 EXEC UpdateCity 1, 'CA', 'Los Angeles' 

This stored procedure deletes a line from the table when given its primary key.

 CREATE PROCEDURE DeleteCity (@CITY_ID int) AS BEGIN     DELETE FROM Cities WHERE CITY_ID = @CITY_ID END 

You can execute this stored procedure using the following syntax:

 EXEC DeleteCity 1 

You can use a stored procedure to wrap a SELECT statement, although generally you could also do the same by using a UDF. Since a stored procedure does not have an explicit return value, the trick is to place a SELECT query as the last statement so that it will become the stored procedures result set.

The following stored procedure retrieves all of the cities in a specific state and returns the result set.

 CREATE PROCEDURE GetCitiesFromState (@STATE char(2)) AS BEGIN    SELECT * FROM Cities WHERE @STATE = STATE END 

Using Stored Procedures and UDFs

Stored procedures and UDFs are programs that run in the database server. Both can be used to return values and result sets. However, this is where their similarities end. The main differences between the two types of server programs are detailed in Table 9-1.

Table 9-1: Database Server Programs

Stored Procedures	User-Defined Functions
Cannot be used as a function inside a query	Can be used as a function inside a query
Can change the database	Cannot change the database
Do not have an explicit return type, although they can return values and tables	Have an explicit return type through the RETURNS clause
Called without parentheses	Called with parentheses
Can call any function	Must be deterministic, must always return the same value for a given set of parameters, and must not have side effects

Note that stored procedures are analogous to Visual Basic subroutines; UDFs are analogous to Visual Basic functions.