Export (0) Print
Expand All

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

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.

NoteNote

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.

To build and deploy this function using Visual Studio

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

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

  3. In the Add New Item dialog box, select User-Defined Function.

  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 that follows this procedure.

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

    NoteNote

    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.

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

    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.

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


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)

Community Additions

ADD
Show:
© 2014 Microsoft