Microsoft SQL Server Create CLR Function in SQL Server


If you have created .Net function, compiled it into .dll, and imported it into SQL server as an assembly, you can create user-defined function that references function in that assembly:

CREATE FUNCTION dbo.TextCompress(@input nvarchar(max)) 
RETURNS varbinary(max) 
AS EXTERNAL NAME MyLibrary.[Name.Space.ClassName].TextCompress 

You need to specify name of the function and signature with input parameters and return values that match .Net function. In AS EXTERNAL NAME clause you need to specify assembly name, namespace/class name where this function is placed and name of the method in the class that contains the code that will be exposed as function.

You can find information about the CLR functions using the following query:

SELECT * FROM dbo.sysobjects WHERE TYPE ='FS'