How to: Create and Run a CLR SQL Server User-Defined Function  Home
This page is specific to:.NET Framework Version:2.03.54.0
Data Access in Client and Middle-Tier Programming 
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

Copy to ClipboardPrint
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

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 Content

SQL CLR Help
Added by:Thomas Lee
Refer to my blog for helpful advice on SQL CLR development.

Jeff Fischer
http://twitter.com/scoftwaredev
http://twitter.com/scoftware
© 2009 Microsoft Corporation. All rights reserved.   Terms of Use | Trademarks | Privacy Statement
Page view tracker
Rate the Lightweight library
x
Lightweight builds on ScriptFree (loband) by adding features you've requested: a SearchBox and default code language selection.
Do you like the SearchBox?
Do you like the tabbed code blocks?
How useful is this topic?
Tell us more.
Thanks
x
You're helping to improve MSDN Online.
Feedback
Switch View
x
Classic
Lightweight Beta
ScriptFree
Switch View