Thursday, 11 December 2008

Create CLR function in SQL Server 2005

-- Drops existing aggregate function, remove this line if not needed
DROP AGGREGATE StringConcat
GO
-- Drops existing assembly, remove this line if not needed
DROP ASSEMBLY SqlServerHelper
GO
CREATE ASSEMBLY SqlServerHelper from '\\AssemblyPath\SqlServerHelper.dll'
WITH PERMISSION_SET = SAFE
GO
EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;

CREATE AGGREGATE StringConcat (@value varchar(4000)) RETURNS nvarchar(4000)
EXTERNAL NAME SqlServerHelper.Concatenate

-- Sample execution of CRL aggregate method
select top 10 dbo.StringConcat(ExternalCode)
from decodeValues group by DirectionCode

* This source code was highlighted with Source Code Highlighter.

No comments: