SQL Server: CREATE FUNCTION with a RETURNS Clause

Function, named GetSample, accepts a single input parameter and returns a result set containing a random sampling of data from several tables in current database

CREATE ASSEMBLY CLRAssembly

FROM 'C:\CLRTest.dll'
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION MyFunction (@parm int)
RETURNS varchar
AS EXTERNAL NAME
AssemblyName.NamespaceName.ClassName.GetSample;

DBA/Developer issued the CREATE FUNCTION statement, DBA/Developer specified that the function returned a scalar value. DBA/Developer need to re-create the function with a RETURNS clause that specifies a tabular result set instead of a scalar value. DBA/Developer would specify the details of the columns returned by the GetSample CLR UDF. DBA/Developer should not re-register the assembly with the EXTERNAL_ACCESS permission set and re-create the function as an inline table-valued function. In this scenario, there is no need for the assembly to be given the EXTERNAL_ACCESS permission set because it uses tables within DBA/Developer current database.

The EXTERNAL_ACCESS permission set allows access to the registry, the file system, environment variables, and unmanaged code. In addition, DBA/Developer would not re-create the function as an inline table-valued function. An inline table-valued function would not be able to provide the details of the table returned by the GetSample function. Inline table-valued functions are defined by including a single SELECT statement without specifically naming the columns of the result. For example, the following statement would create an inline-table valued function without specifying the details of the returned table value.

CREATE FUNCTION dbo.GetData (@parm int)

RETURNS table
AS RETURN (SELECT * FROM MyTable WHERE MyColumn = @parm);

DBA/Developer should not remove the EXTERNAL NAME clause from the CREATE FUNCTION statement and drop and re- create the function. The EXTERNAL NAME clause must be specified when creating a CLR UDF to identify the .NET function that will be referenced. DBA/Developer should not re-create the desired functionality using a Transact-SQL stored procedure. In this scenario, DBA/Developer can access the previously created CLR function without additional development effort.