How to: Create and Run a SQL Server User-Defined Function by using Common Language Run-time Integration

Article
07/25/2011

Create a SQL user-defined function by adding a User-Defined Function to a SQL Server Common Language Run-time (SQL CLR) database project. After successful deployment, the user-defined function can be called and executed.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

Creating SQL Server User-Defined Functions

To build and deploy this function using Visual Studio

Open an existing SQL CLR Database Project, or create a new one. For more information, see How to: Create a Project for Database Objects that Use SQL Server Common Language Run-time Integration.
On the Project menu, select Add New Item.
In the Add New Item dialog box, select User-Defined Function.
Type a Name for the new user-defined function.
Add code to run when the user-defined function is executed. See the first example that follows this procedure.
In Solution Explorer, open the TestScripts folder and double-click the Test.sql file to open it for editing. Add code to execute your user-defined function. See the second example that follows this procedure.

Note

You can specify other scripts as your default debug script. For more information, see How to: Edit the Test.sql Script to Run Objects that use SQL Server Common Language Run-time Integration.
Deploy the user-defined function to the SQL Server. For more information, see How to: Deploy SQL Server CLR Integration Database Project Items to a SQL Server.

Important

SQL Server 2005 and SQL Server 2008 only support SQL Server projects that were built with the 2.0, 3.0, or 3.5 version of the .NET Framework. If you try to deploy a SQL Server project to SQL Server 2005 or SQL Server 2008, an error appears: Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database (where AssemblyName is the name of the assembly that you are deploying). For more information, see How to: Create a Project for Database Objects that Use SQL Server Common Language Run-time Integration.
Press F5 to debug the user-defined function by executing it on the SQL Server.

Description

The following code example creates a user-defined scalar function called addTax that takes a price as a parameter, adds sales tax to it, and returns the price plus the tax.

After you create the function, deploy it to the SQL Server. For more information, see How to: Deploy SQL Server CLR Integration Database Project Items to a SQL Server

Code

Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions

    Public Const SALES_TAX As Double = 0.086

    <SqlFunction()> 
    Public Shared Function addTax(ByVal originalAmount As SqlDouble) As SqlDouble

        Dim taxAmount As SqlDouble = originalAmount * SALES_TAX

        Return originalAmount + taxAmount
    End Function
End Class

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    public const double SALES_TAX = .086;

    [SqlFunction()]
    public static SqlDouble addTax(SqlDouble originalAmount)
    {
        SqlDouble taxAmount = originalAmount * SALES_TAX;

        return originalAmount + taxAmount;
    }
}

Description

Add code to test the user-defined function to the Test.sql file in the TestScripts folder in your project. For example, to test this function, use a query, such as "SELECT dbo.addTax(10)." You should see the returned value "10.86."

Code

SELECT dbo.addTax(10)

Share via

How to: Create and Run a SQL Server User-Defined Function by using Common Language Run-time Integration

Creating SQL Server User-Defined Functions

To build and deploy this function using Visual Studio

Description

Code

Description

Code

See Also

Tasks

Reference

Concepts

Other Resources

Additional resources