Previous versions of SQL Server have had only a T-SQL version of user-defined functions. As with stored procedures, you can now create user-defined functions using managed code and the SQLCLR.
A user-defined function is a T-SQL or managed code routine that is stored in the database and returns a scalar value or table value. User-defined functions can be called from within T-SQL statements.
A user-defined function should never attempt to alter database data or schema. This rule is enforced with T-SQL and with the context connection, but if you use a regular connection to modify data, no exception is thrown. In any case, you should always avoid modifying the database from within a user-defined function.
We examine the scalar function first, and then we look at the table value function.
Using Scalar Functions
Scalar functions return a single scalar value. The type can be any SQL scalar type except text, ntext, image, or timestamp. You can specify managed data types but not alias data types. Also, for SQLCLR functions, you can specify nchar and nvarchar but not char and varchar.
One example of a scalar function is a function that puts leading zeros in front of numbers so they line up properly in a report. Add a function to your example project as follows:
Visual Basic
Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class UserDefinedFunctions <Microsoft.SqlServer.Server.SqlFunction()> _ Public Shared Function PadVb( _ ByVal inputValue As integer, _ ByVal width As Integer) As SqlString Return New SqlString(inputValue.ToString().PadLeft(width, "0"c)) End Function End Class
C#
using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString PadCs(int inputValue, int width) { return new SqlString(inputValue.ToString().PadLeft(width, '0')); } }
Add the following SQL script to the Test.sql file that will use this scalar function to pad the SupplierID field with leading zeros for a total width of five characters. Notice the use of dbo to access the function. User-defined functions always require the owner prefix when you access them.
Visual Basic
Select Top dbo.PadVb(supplierid,5) as ID,CompanyName from suppliers
C#
Select dbo.PadCs(supplierid,5) as ID,CompanyName from suppliers
In your output window, you will notice that the ID column is very wide; the function was registered in SQL Server with a return type of nvarchar(4000), so the output window doesn't know how wide the column really is. You could alter the registration to set the return data type to nvarchar(5), but you would have a problem if you passed a width other than 5 into the function. The good news is that the values returned were only 5 characters long. The output window will look like the following (except I removed the extra spaces in the ID column).
ID CompanyName------ ----------------------------------------00018 Aux joyeux eccl siastiques00016 Bigfoot Breweries00005 Cooperativa de Quesos 'Las Cabras'00027 Escargots Nouveaux00001 Exotic Liquids00029 For ts d' rables00014 Formaggi Fortini s.r.l.00028 Gai p turage00024 G'day, Mate00003 Grandma Kelly's Homestead00011 Heli S waren GmbH & Co. KG00023 Karkki Oy00020 Leka Trading00021 Lyngbysild00025 Ma Maison00006 Mayumi's00019 New England Seafood Cannery00002 New Orleans Cajun Delights00013 Nord-Ost-Fisch Handelsgesellschaft mbH00015 Norske Meierier00026 Pasta Buttini s.r.l.00007 Pavlova, Ltd.00009 PB Kn ckebr d AB00012 Plutzer Lebensmittelgro m rkte AG00010 Refrescos Americanas LTDA00008 Specialty Biscuits, Ltd.00017 Svensk Sj f da AB00004 Tokyo Traders00022 Zaanse SnoepfabriekNo rows affected.(29 row(s) returned)
You can view the function registration information in Server Explorer in Visual Studio. Figure 9-5 shows the Server Explorer window and the functions we created. Clicking a function or one of the parameters displays its properties in the Properties window.
Figure 9-5: You can view the function registration information in Server Explorer and the Properties window.
Sometimes you will want to manually register the function in SQL Server. The following SQL script shows the syntax.
SQL: Scalar Function Registration
CREATE FUNCTION <function name>(<parameter list>)RETURNS <return type>AS EXTERNAL NAME <assembly identifier>.<type name>.<method name>
Using a Streaming Table-Valued Function (TVF)
You can also create a user-defined function that returns a table. When you use the SQLCLR to create one of these functions, it is called a streaming table-valued function, or streaming TVF. Streaming TVFs don't return rowsets to the client, as stored procedures do. Instead, you can use streaming TVFs in a SQL statement where a table is expected. For example, you can use a streaming TVF after the word FROM in a SQL SELECT statement.
Streaming TVFs are so named because a typical T-SQL TVF temporarily stores its results in a work table, whereas the SQLCLR TVF is capable of streaming the results it produces the results don't need to be fully materialized before returning from the function.
The streaming TVF requires a return type of IEnumerable, and you must set the FillRowMethodName property to the name of a method that will be used to break an object into the columns required for a row in the returning rowset. The method signature for the fill row method is based on the schema of the row that is being returned.
An example of a streaming TVF is one that accepts a folder name as a parameter and a filter pattern as a parameter and returns a list of file information as a table that can be filtered with a WHERE clause or sorted with the ORDER BY clause (as shown in the following code sample).
Visual Basic
Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.IO Imports System.Collections Imports System.Security.Principal Partial Public Class UserDefinedFunctions <Microsoft.SqlServer.Server.SqlFunction( _ FillRowMethodName:="FillRow", _ TableDefinition:="Name nvarchar(32), Length bigint, Modified DateTime")> _ Public Shared Function FileListVb( _ ByVal directoryName As String, ByVal pattern As String) _ As IEnumerable Dim files As FileInfo() Dim OriginalContext As WindowsImpersonationContext OriginalContext = SqlContext.WindowsIdentity.Impersonate() Try Dim di As New DirectoryInfo(directoryName) files = di.GetFiles(pattern) Finally If Not (OriginalContext Is Nothing) Then OriginalContext.Undo() End If End Try Return files End Function ' The fill row method that cracks the FileRecord ' and returns the individual columns. Public Shared Sub FillRow(ByVal Obj As Object, _ ByRef fileName As SqlString, _ ByRef fileLength As SqlInt64, _ ByRef fileModified As SqlDateTime) If Not (Obj Is Nothing) Then Dim file As FileInfo = CType(Obj, FileInfo) fileName = file.Name fileLength = file.Length fileModified = file.LastWriteTime Else fileName = SqlString.Null fileLength = SqlInt64.Null fileModified = SqlDateTime.Null End If End Sub End Class
C#
using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Collections; using System.IO; using System.Security.Principal; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillRow", TableDefinition = "Name nvarchar(32), Length bigint, Modified DateTime")] public static IEnumerable FileListCs(string directoryName, string pattern) { FileInfo[] files; WindowsImpersonationContext OriginalContext; OriginalContext = SqlContext.WindowsIdentity.Impersonate(); try { DirectoryInfo di = new DirectoryInfo(directoryName); files = di.GetFiles(pattern); } finally { if (OriginalContext != null) { OriginalContext.Undo(); } } return files; } // The fill row method that cracks the FileRecord // and returns the individual columns. public static void FillRow(object Obj, ref SqlString fileName, ref SqlInt64 fileLength, ref SqlDateTime fileModified) { if (Obj != null) { FileInfo file = (FileInfo)Obj; fileName = file.Name; fileLength = file.Length; fileModified = file.LastWriteTime; } else { fileName = SqlString.Null; fileLength = SqlInt64.Null; fileModified = SqlDateTime.Null; } } }
The FileList function is a streaming TVF, and streaming TVFs are required to return an IEnumerator type. The easiest way to satisfy this requirement is to return an array or some other collection that already implements the IEnumerator interface. An array of FileInfo objects works well.
The FileList function uses impersonation to access the file system, using the credentials of the caller rather the credentials of the SQL service account. After the file information is retrieved, impersonation is undone. This works as long as the directory you are accessing is on the SQL Server machine. Remember that access to a remote machine requires delegation, which must be enabled on an account-by-account basis by the Windows domain administrator and is described in detail in Chapter 13.
The FillRow method is responsible for filling a row of data based on the object being passed in. Notice that the parameters are specific to this function, so the method signature varies based on your function.
You must specify the SqlFunction attribute's TableDefinition and FillRowMethodName when you create streaming TVFs. The TableDefinition is simply the schema information for the rowset that will be returned. (Note that the file name is set to a measly 32 characters for the purpose of rendering in this book.)
To test this function, add the following script to the Test.sql file.
Visual Basic
Select * from dbo.FileListVb('C:\','*.*')
C#
Select * from dbo.FileListCs('C:\','*.*')
Here is the output of this function when executed on my SQL Server machine. Your results will vary.
