This example will create a function which will use a mathematical equation to change power/quantity/constant1/constant2 in to a number. The equation is of the form ‘P*Q*K1+K1’.
Code example:
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Data;
using System;
public partial class UserDefinedFunctions
{
[SqlProcedure]
public static SqlDouble EvaluateMaths(SqlDouble power, SqlDouble quantity, SqlDouble constant1, SqlDouble constant2, SqlString maths)
{
DataTable dt = new DataTable();
string s = maths.ToString();
s = s.Replace("P", power.ToString());
s = s.Replace("Q", quantity.ToString());
s = s.Replace("K1", constant1.ToString());
s = s.Replace("K2", constant2.ToString());
var v = dt.Compute(s, "");
return Convert.ToDouble(v);
}
}
What to do:
Use csc.exe to compile this file in to a DLL:
C:\Windows\Microsoft.NET\Framework\v4.0.30319\csc.exe -out:c:\temp\EvaluateMaths.dll c:\temp\EvaluateMaths.cs
Next you need to import the DLL and make an external function from the exported function:
create assembly EvaluateMaths from 'c:\temp\EvaluateMaths.dll' with permission_set = safe;
go
create function EvaluateMaths(
@power float,
@quantity float,
@K1 float,
@K2 float,
@function nvarchar(max))
returns float with execute as caller, returns null on null input
as
external name EvaluateMaths.UserDefinedFunctions.EvaluateMaths
go
sp_configure 'clr enabled', 1
go
reconfigure
go
Now you can use the function like any normal scalar valued function:
select dbo.EvaluateMaths(1,2,3,4,'P+Q');
You can then use this function in things like Custom Function Facts to generate row based mathematical formulas.
Example:
Resources:
Mapping from SQL to .Net types
A simple CLR function example
http://www.codemahek.com/blog/execute-c-methods-from-sql-server
Comments
0 comments
Please sign in to leave a comment.