Предложение T-SQL, возвращающее информацию о сборке
SELECT name AS [Name], SCHEMA_NAME(principal_id) AS [Schema],
	assembly_id AS [ID], permission_set AS [PermissionSet],
	is_visible AS [IsVisible], create_date AS [CreateDate],
	CONVERT(int, ASSEMBLYPROPERTY(name, ?VersionMajor?)) AS [MajorVersion],
	CONVERT(int, ASSEMBLYPROPERTY(name, ?VersionMinor?)) AS [MinorVersion],
	CONVERT(int, ASSEMBLYPROPERTY(name, ?VersionBuild?)) AS [BuildVersion],
	CONVERT(int, ASSEMBLYPROPERTY(name, ?VersionRevision?)) AS [RevisionVersion]
FROM sys.assemblies
WHERE name=N?SQLMagDemo?

/***********************************************************
Author		: Vinod Kumar
Article for	: SQL Server Magazine
***********************************************************/

—
— Demo script used to create an temporary table and test
— CLR User defined Aggregate function
—
Create table #temp_dept (Emp_id Int, Dept_id Int, product_key Int, Name Varchar(30))
Insert into #temp_dept values (1, 1, 1, ?Vinod?)
Insert into #temp_dept values (2, 1, 10, ?Kumar?)
Insert into #temp_dept values (3, 2, 15, ?Sara?)
Insert into #temp_dept values (4, 2, 3, ?Bob?)
Insert into #temp_dept values (5, 2, 2, ?Agarwal?)
Insert into #temp_dept values (6, 3, 30, ?TestName?)

—
— This uses the Product CLR function
—
Select Dept_id, dbo.clr_product(product_key) Product from #temp_dept
Group By Dept_id

—
— This uses the String Concatenation CLR function
—
Select Dept_ID, dbo.clr_concat(name) [Emp_Names] from #temp_dept
Group by Dept_ID

Drop table #temp_dept
GO

—
— Invoking a CLR Stored Procedure from T-SQL
— Call a Stored Procedure
—
Exec dbo.clr_FixedDrives
GO

—
— Invoking a CLR Trigger from T-SQL
—
Create table vin_EmailTest (Name Varchar(50), EmailID Varchar(50), SSN INT)
select * from vin_EmailTest
— Below will raise an error
insert into vin_EmailTest values (?a?, ?b?, 432)

— This will succeed as the Email ID is valid
insert into vin_EmailTest values (?vinod?, ?vinod@hotmail.com?, 432)
GO

Поделитесь материалом с коллегами и друзьями