How to: Create and Run a CLR SQL Server User-Defined Function

Article
11/16/2012

Create a SQL user-defined function by adding a User-Defined Function to a SQL Server project. After successful deployment, the user-defined function can be called and executed.

Note

By default, the common language runtime (CLR) integration feature is off in Microsoft SQL Server and must be enabled in order to use SQL Server project items. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure. For more information, see Enabling CLR Integration.

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 Server Project, or create a new one. For more information, see How to: Create a SQL Server Project.
From the Project menu, select Add New Item.
Select User-Defined Function in the Add New Item Dialog Box.
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.

Note

C++ examples must be compiled with the /clr:safe compiler option.
For Visual Basic and Visual C#, 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.

For Visual C++, in Solution Explorer, double-click the debug.sql file to open it for editing. Add code to execute your user-defined function. See the second example that follows this procedure.
Deploy the user-defined function to the SQL Server. For more information, see How to: Deploy SQL Server Project Items to a SQL Server.
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 creating the function, deploy it to the SQL Server. For more information, see How to: Deploy SQL Server 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;
    }
}

#include "stdafx.h"

#using <System.dll>
#using <System.Data.dll>
#using <System.Xml.dll>

using namespace System;
using namespace System::Data;
using namespace System::Data::Sql;
using namespace System::Data::SqlTypes;
using namespace Microsoft::SqlServer::Server;

// In order to debug your Aggregate, add the following to your debug.sql file: 
// 
// SELECT dbo.addTax(10) 
// 

public ref class UserDefinedFunctions
{
public:
    static initonly double SALES_TAX = 0.086;

    [SqlFunction()]
    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 (debug.sql in Visual C+) 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 CLR SQL Server User-Defined Function

Creating SQL Server User-Defined Functions

To build and deploy this function using Visual Studio

Description

Code

Description

Code

See Also

Tasks

Concepts

Reference

Other Resources

Additional resources