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.
.gif) Note |
|---|
| 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. |
| Note |
|---|
| 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
-
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 below.
| 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 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.
-
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.
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
|
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;
}
};
|
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."
See Also