SQLCLR: Create a simple table-valued function

1. Create the project:

2. Add the function code:

Using system;using system.data.sql;using microsoft.sqlserver.server;using system.collections;using System.data.sqltypes;using System.diagnostics;public class tabulareventlog{    [SqlFunction (TableDefinition = @ " logtime datetime,message nvarchar (4000), Category nvarchar (4000), InstanceId bigint ",        Name =" Readeventlog ", FillRowMethodName = "FillRow")] public    static IEnumerable Initmethod (String logname)    {        return new EventLog (LogName, Environment.MachineName). Entries;    }    public static void FillRow (Object-obj, out-SqlDateTime-TimeWritten, out-        SqlChars-message, out-SqlChars category,        O UT long instanceId)    {        EventLogEntry eventlogentry = (eventlogentry) obj;        TimeWritten = new SqlDateTime (eventlogentry.timewritten);        message = new SqlChars (eventlogentry.message);        Category = new SqlChars (eventlogentry.category);        InstanceId = Eventlogentry.instanceid;    }}

3. Script:

Use Mastergosp_configure ' Show advanced options ', 1; Goreconfigure; Gosp_configure ' CLR enabled ', 1; Goreconfigure; The go--table-valued function is placed on the Db_study library with the use db_studygo--delete function if object_id (' [dbo].[ Readeventlog] is not nulldrop FUNCTION [dbo]. readeventloggo--removes the assembly if EXISTS (SELECT * from SYS. Assemblies WHERE name= ' Tvfeventlog ') DROP  ASSEMBLY tvfeventloggo--CREATE assembly, set to actual path, note should be set to: Unsafecreate ASSEMBLY Tvfeventlog from  ' D:\Project\StudySimple\SqlServerProject1\bin\Debug\SqlServerProject1.dll ' with Permission_ SET = unsafego--Creates a table-valued function, create functions dbo. Readeventlog (@logname nvarchar) RETURNS TABLE (logtime datetime,message nvarchar (4000), Category nvarchar (4000), InstanceId BIGINT) asexternal NAME tvfEventLog.TabularEventLog.InitMethod go--Query Select TOP t.logtime, T.message, T. INSTANCEIDFROM dbo. Readeventlog (N ' Security ') as Torder by LogTime DESC

Reference: Http://www.microsoft.com/china/msdn/library/data/sqlserver/bb293147.mspx?mfr=true

There is a problem with this page: https://msdn.microsoft.com/zh-cn/library/ms131103 (v=sql.120). aspx

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

SQLCLR: Create a simple table-valued function