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('MinOf') IS NOT NULL
    DROP AGGREGATE MinOf
GO

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

IF OBJECT_ID('CreateBasicMarginalValueModel') IS NOT NULL

    DROP AGGREGATE CreateBasicMarginalValueModel
GO

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

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

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

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

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

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

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

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

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

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

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

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

IF (SELECT COUNT(*) FROM sys.types WHERE UPPER(name) = 'MARGINALVALUEMODELARGS') > 0 
    DROP TYPE MarginalValueModelArgs
GO
IF (SELECT COUNT(*) FROM sys.types WHERE UPPER(name) = 'BASICMARGINALVALUEMODEL') > 0 
    DROP TYPE BasicMarginalValueModel
GO

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

CREATE ASSEMBLY ud
FROM 'c:\\gordon\blog\blog3.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 ud.AnyType
GO

CREATE TYPE ValuePair EXTERNAL NAME ud.ValuePair
GO

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

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

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

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

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

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

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

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

CREATE TYPE MarginalValueModelArgs EXTERNAL NAME ud.MarginalValueModelArgs
GO

CREATE TYPE BasicMarginalValueModel EXTERNAL NAME ud.BasicMarginalValueModel
GO

CREATE AGGREGATE CreateBasicMarginalValueModel (@args MarginalValueModelArgs)
RETURNS BasicMarginalValueModel
EXTERNAL NAME ud.CreateBasicMarginalValueModel
GO

CREATE FUNCTION MarginalValueArgs1 (@dim1 nvarchar(2000), @wgt float)
RETURNS MarginalValueModelArgs
EXTERNAL NAME ud.UserDefinedFunctions.MarginalValueArgs1
GO

CREATE FUNCTION MarginalValueArgs2 (@dim1 nvarchar(2000), @dim2 nvarchar(2000), @wgt float)
RETURNS MarginalValueModelArgs
EXTERNAL NAME ud.UserDefinedFunctions.MarginalValueArgs2
GO


PRINT 'Loaded BasicMarginalValueModel'
PRINT 'Done'