Export (0) Print
Expand All

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

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.

NoteNote

The common language runtime (CLR) integration feature is off by default 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. From more information, see Enabling CLR Integration.

NoteNote

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

Creating SQL Server User-Defined Functions

To build and deploy this function using Visual Studio

  1. Open an existing SQL Server Project, or create a new one. For more information, see How to: Create a SQL Server Project.

  2. From the Project menu, select Add New Item.

  3. Select User-Defined Function in the Add New Item Dialog Box.

  4. Type a Name for the new user-defined function.

  5. Add code to run when the user-defined function is executed. See the first example below.

NoteNote

C++ examples must be compiled with the /clr:safe compiler option.

  1. 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 below.

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

  2. Deploy the user-defined function to the SQL Server. For more information, see How to: Deploy SQL Server Project Items to a SQL Server.

  3. Press F5 to debug the user-defined function by executing it on the SQL Server.

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

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;
    }
}

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

SELECT dbo.addTax(10)

See Also

Community Additions

ADD
Show:
© 2014 Microsoft