USE ud
GO

sp_configure 'clr_enabled', 1
GO
RECONFIGURE
GO

IF OBJECT_ID('Wavg') IS NOT NULL
    DROP AGGREGATE Wavg
GO

IF OBJECT_ID('CreateWeightedValue') IS NOT NULL
    DROP FUNCTION CreateWeightedValue
GO

IF (SELECT COUNT(*) FROM sys.types WHERE UPPER(name) = 'WEIGHTEDVALUE') > 0 
    DROP TYPE WeightedValue
GO

IF (SELECT COUNT(*) FROM sys.assemblies WHERE UPPER(name) = 'UD') > 0
    DROP ASSEMBLY ud
GO

CREATE ASSEMBLY ud
FROM 'c:\\gordon\blog\blog2.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE TYPE WeightedValue EXTERNAL NAME ud.WeightedValue;
GO

CREATE FUNCTION CreateWeightedValue (@val float, @wgt float)
RETURNS WeightedValue as
EXTERNAL NAME ud.UserDefinedFunctions.CreateWeightedValue
GO

CREATE AGGREGATE WAvg (@val WeightedValue)
RETURNS float 
EXTERNAL NAME ud.WAvg
GO
PRINT 'Loaded WAvg()'
GO

CREATE TYPE AnyType EXTERNAL NAME udf.AnyType
GO

CREATE TYPE ValuePair EXTERNAL NAME udf.ValuePair
GO

CREATE FUNCTION AnyInt (@val int)
RETURNS AnyType
EXTERNAL NAME udf.UserDefinedFunctions.AnyInt
GO

CREATE FUNCTION AnyBigInt (@val bigint)
RETURNS AnyType
EXTERNAL NAME udf.UserDefinedFunctions.AnyBigInt
GO

CREATE FUNCTION AnyDecimal (@val decimal)
RETURNS AnyType
EXTERNAL NAME udf.UserDefinedFunctions.AnyDecimal
GO

CREATE FUNCTION AnyString (@val nvarchar(2000))
RETURNS AnyType
EXTERNAL NAME udf.UserDefinedFunctions.AnyString
GO

CREATE FUNCTION AnyDouble (@val float)
RETURNS AnyType
EXTERNAL NAME udf.UserDefinedFunctions.AnyDouble
GO

CREATE FUNCTION ValuePair (@val1 AnyType, @val2 AnyType)
RETURNS ValuePair
EXTERNAL NAME udf.UserDefinedFunctions.ValuePair
GO

CREATE AGGREGATE MinOf (@val ValuePair)
RETURNS AnyType
EXTERNAL NAME udf.MinOf
GO

CREATE AGGREGATE MaxOf (@val ValuePair)
RETURNS AnyType
EXTERNAL NAME udf.MaxOf
GO
PRINT 'Loaded MinOf() and MaxOf()'

PRINT 'Done'