SQL2005 中使用UDF

For TSQL:

USE AdventureWorks;

GO

CREATE FUNCTION Sales.fn_SalesByStore (@storeid int)

RETURNS TABLE

AS

RETURN

(

    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'

    FROM Production.Product AS P

      JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID

      JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID

    WHERE SH.CustomerID = @storeid

    GROUP BY P.ProductID, P.Name

);

GO

 

 

For C#:

 

using System;

using System.Data.Sql;

using Microsoft.SqlServer.Server;

using System.Collections;

using System.Data.SqlTypes;

using System.Diagnostics;

 

public class TabularEventLog

{

    [SqlFunction(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, out long instanceId)

    {

        EventLogEntry eventLogEntry = (EventLogEntry)obj;

        timeWritten = new SqlDateTime(eventLogEntry.TimeWritten);

        message = new SqlChars(eventLogEntry.Message);

        category = new SqlChars(eventLogEntry.Category);

        instanceId = eventLogEntry.InstanceId;

    }

}

 

You need to create the function in SQL by running these TSQL statement:

 

CREATE ASSEMBLY tvfEventLog

FROM'D:/assemblies/tvfEventLog/tvfeventlog.dll'

WITH PERMISSION_SET = UNSAFE

GO

CREATE FUNCTION ReadEventLog(@logname nvarchar(100))

RETURNS TABLE

(logTime datetime,Message nvarchar(4000),Category nvarchar(4000),InstanceId bigint)

AS

EXTERNAL NAME tvfEventLog.TabularEventLog.InitMethod

GO

 

And here is the example on how you can run your CLR functions:

 

-- select the top 100 events

SELECT TOP 100 *

FROM dbo.ReadEventLog(N'Security') as T

go

 

-- select the last 10 login events

SELECT TOP 10 T.logTime, T.Message, T.InstanceId

FROM dbo.ReadEventLog(N'Security') as T

WHERE T.Category = N'Logon/Logoff'

Go