用Clr实现的sql表值函数splitIDs-技术开发专区

用Clr实现的sql表值函数splitIDs

作者：ITPUB论坛编辑： nancy 2008-05-23 00:00

【IT168技术文档】

在我们需要批量删除数据，或者批量修改实体的状态时，为了性能我们会直接写一个存储过程，并将这一批数据的id用“,”分隔传递给一个存储过程，然后在存储过程中拆分这个字符串，然后执行删除或者更新状态操作。以前每次执行这种操作时我都会在存储过程中拆分字符串。现在sql server支持用.net clr的程序集写函数，存储过程等等。

现在我们就牛刀小试，做一个clr的sql表值函数。该函数的功能就是传入一个用逗号分隔的数字id字符，返回一个只有一列id的表。
第一步：我们需要新建一个类库项目，并添加一个类SplitIDs

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
public class SplitIDs
{
[SqlFunction(FillRowMethodName = "FillRow")]
public static IEnumerable DoSplit(String strIDs)
{
return strIDs.Split(',');
}
public static void FillRow(Object obj, out SqlInt64 id)
{
long value = 0;
long.TryParse((string)obj, out value);
id = new SqlInt64(value);
}
}

有2个注意的点
1. 命名空间声明要去掉，我在测试的过程中刚开始有命名空间的声明，总是注册不成功，后来去掉了存储过程的声明，才注册上
2. 方法必须是静态的并且要有SqlFunction特性，表值函数的返回值是IEnurable

第二步：注册程序集到sql server中

USE [DB_Name]
GO
if (object_id('SplitIDs') is not null)
drop function splitIds;
GO
IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SqlServerUtility')
DROP ASSEMBLY [SqlServerUtility]
go
CREATE ASSEMBLY SqlServerUtility
FROM 'D:\Program Files\Microsoft SQL Server\90\UserDefinedAssembly\SqlServerUtility.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION SplitIDs(@ids Nvarchar(max))
RETURNS TABLE (id bigint)
AS
EXTERNAL NAME SqlServerUtility.SplitIDs.DoSplit
GO
EXEC sp_configure "clr enabled",1
RECONFIGURE
GO

关注我们