sql怎么建表内值函数

发布网友发布时间：2022-04-30 08:29

共2个回答

懂视网时间：2022-04-30 12:51

1. 创建项目：

技术分享

2. 添加函数代码：

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,
 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;
 }
}

3. 脚本：

USE MASTER
GO
sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
--表值函数放在 db_study 库上
USE db_study
GO
--删除函数
IF OBJECT_ID('[dbo].[ReadEventLog]') IS NOT NULL
	DROP FUNCTION [dbo].ReadEventLog
GO
--删除程序集
IF EXISTS(SELECT * FROM SYS.ASSEMBLIES WHERE NAME='tvfEventLog') 
	DROP ASSEMBLY tvfEventLog
GO
--创建程序集, 设置为实际路径， 注意应设置为: UNSAFE
CREATE ASSEMBLY tvfEventLog FROM'D:ProjectStudySimpleSqlServerProject1inDebugSqlServerProject1.dll' WITH PERMISSION_SET = UNSAFE
GO
--创建表值函数
CREATE FUNCTION dbo.ReadEventLog(@logname nvarchar(100))
RETURNS TABLE (
	logTime DATETIME
	,Message nvarchar(4000)
	,Category nvarchar(4000)
	,InstanceId BIGINT
)
AS
	EXTERNAL NAME tvfEventLog.TabularEventLog.InitMethod 
GO
--查询
SELECT TOP 10 T.logTime, T.Message, T.InstanceId
FROM dbo.ReadEventLog(N'Security') as T
ORDER BY logTime DESC

技术分享

参考：http://www.microsoft.com/china/msdn/library/data/sqlserver/bb293147.mspx?mfr=true

此页面做法有问题： https://msdn.microsoft.com/zh-cn/library/ms131103(v=sql.120).aspx

SqlClr：创建一个简单的表值函数

标签：sqlclr

热心网友时间：2022-04-30 09:59

你说表值函数吗？
有两种形式
1
create function f_t (@xx int)
returns table(id int,c varchar(10)) --这里是写出表结构
as
begin
return xxx ---
end
2
create function f_t(xxxx)
returns table
as
return ( select xxxxx ....)
--- 直接反回