ADO.NET 3.5 Cookbook, 2nd Edition

12.2. Creating a Scalar-Valued Function

Problem

You need to create a CLR scalar-valued function.

Solution

The solution creates a scalar-valued function that is a CLR stored procedure that returns the total for a specific sales order by summing the LineTotal values in the Sales.SalesOrderDetail table in AdventureWorks for a specified sales order ID. Follow these steps:

1. Create a new SQL Server project in Visual Studio and name it ClrScalarValuedFunction.

2. Create a user-defined function item in the project. Name the item SumLineTotal.cs.

3. The C# code in SumLineTotal.cs in the project ClrScalarValuedFunction is shown in Example 12-5.

   Example 12-5. File: SumLineTotal.cs

   using System;
   using System.Data;
   using System.Data.SqlClient;
   using System.Data.SqlTypes;
   using Microsoft.SqlServer.Server;
   
   public partial class UserDefinedFunctions
   {
       [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
       public static SqlMoney SumLineTotal(int salesOrderID)
       {
           using (SqlConnection connection =
               new SqlConnection("context connection=true"))
           {
               connection.Open(  );
               SqlCommand cmd = new SqlCommand(
                   "SELECT SUM(LineTotal) " +
                   "FROM Sales.SalesOrderDetail " +
                   "WHERE SalesOrderID=" + salesOrderID, connection);
               return (decimal)cmd.ExecuteScalar(  );
           }
       }
   };

   Notice that the function returns data type SqlMoney and accepts an argument salesOrderID of data type int. The actual return value is decimal, which is compatible with the SQL Server Money data type. The function reads data from SQL Server, so the