Creating User-Defined Functions

 

Creating User-Defined Functions

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:

image from book

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 
image from book

image from book

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'));    } } 
image from book

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.

image from book

Visual Basic

Select Top dbo.PadVb(supplierid,5) as ID,CompanyName from suppliers 
image from book

image from book

C#

Select dbo.PadCs(supplierid,5) as ID,CompanyName from suppliers 
image from book

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 siastiques 00016  Bigfoot Breweries 00005  Cooperativa de Quesos 'Las Cabras' 00027  Escargots Nouveaux 00001  Exotic Liquids 00029  For ts d' rables 00014  Formaggi Fortini s.r.l. 00028  Gai p turage 00024  G'day, Mate 00003  Grandma Kelly's Homestead 00011  Heli S waren GmbH & Co. KG 00023  Karkki Oy 00020  Leka Trading 00021  Lyngbysild 00025  Ma Maison 00006  Mayumi's 00019  New England Seafood Cannery 00002  New Orleans Cajun Delights 00013  Nord-Ost-Fisch Handelsgesellschaft mbH 00015  Norske Meierier 00026  Pasta Buttini s.r.l. 00007  Pavlova, Ltd. 00009  PB Kn ckebr d AB 00012  Plutzer Lebensmittelgro m rkte AG 00010  Refrescos Americanas LTDA 00008  Specialty Biscuits, Ltd. 00017  Svensk Sj f da AB 00004  Tokyo Traders 00022  Zaanse Snoepfabriek No 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.

image from book
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.

image from book

SQL: Scalar Function Registration

CREATE FUNCTION <function name> ( <parameter list> ) RETURNS <return type> AS EXTERNAL NAME <assembly identifier>.<type name>.<method name> 
image from book

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).

image from book

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 
image from book

image from book

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;       }    } } 
image from book

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.

image from book

Visual Basic

Select * from dbo.FileListVb('C:\','*.*') 
image from book

image from book

C#

Select * from dbo.FileListCs('C:\','*.*') 
image from book

Here is the output of this function when executed on my SQL Server machine. Your results will vary.

Name                             Length                Modified -------------------------------- --------------------- ----------------------- AUTOEXEC.BAT                     0                     4/16/2005 12:19:06 PM boot.ini                         210                   5/1/2005 5:17:26 PM CONFIG.SYS                       0                     4/16/2005 12:19:06 PM IO.SYS                           0                     4/16/2005 12:19:07 PM MSDOS.SYS                        0                     4/16/2005 12:19:07 PM NTDETECT.COM                     47772                 5/1/2005 3:02:40 PM ntldr                            295536                5/1/2005 3:02:40 PM pagefile.sys                     943718400             7/26/2005 8:26:32 PM No rows affected. (8 row(s) returned) 

 


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON

Similar book on Amazon
Beginning Linux Programming
Beginning Linux Programming
Advanced Programming in the UNIX(R) Environment (Addison-Wesley Professional Computing Series)
Advanced Programming in the UNIX(R) Environment (Addison-Wesley Professional Computing Series)
Tom Swan's GNU C++ for Linux (Professional Dev. Guide)
Tom Swan's GNU C++ for Linux (Professional Dev. Guide)

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net