SQL.CLR

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

 

(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

 

(5) Use the created function

call the udf
select dbo.fnHelloWorld()