1 create a CLR User-defined function.
(1) create a Database -- SQL Server Project; Add a new item(User-defined function); Wirte a helloword function
Udf helloworld
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString fnHelloWorld()
{
// Put your code here
return new SqlString("Hello World");
}
};
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString fnHelloWorld()
{
// Put your code here
return new SqlString("Hello World");
}
};
(2) build the project. get the relative assembly(*.dll)
(3) Open the SSMS. Select one database, expand the menu, get the "Assemblies" node, right click, "New assembly", import the previoud assembly. set the other options if possible.
(4) Create one SQL Function, which call the udf in the assembly
Sql function
CREATE FUNCTION fnHelloWorld()
RETURNS NVARCHAR(50) WITH EXECUTE AS CALLER
AS EXTERNAL NAME CLRFunction.UserDefinedFunctions.fnHelloWorld
CREATE FUNCTION fnHelloWorld()
RETURNS NVARCHAR(50) WITH EXECUTE AS CALLER
AS EXTERNAL NAME CLRFunction.UserDefinedFunctions.fnHelloWorld
(5) Use the created function
call the udf
select dbo.fnHelloWorld()
select dbo.fnHelloWorld()