A user defined script functions is a powerful extension mechanism which allows definition of new functions to address specific user needs.
Scalar Valued Functions
A scalar valued function accepts parameters, performs an action, and returns the result as a single (scalar) value. An example of a built-in scalar valued function is SUBSTRING.
Aggregate Functions
An aggregate function performs an action on a set of values and returns a single value. An example of a built-in aggregate function is SUM.
Table Valued Functions
A table valued function return a System.Data.DataTable object and can be used where tables can be referenced.
RegisterScriptFunction
Registers a user defined script function.
 |
This function must be invoked separate from a script that uses the registered function. |
Syntax
RegisterScriptFunction ( name, source, language, type, assemblies )
Arguments
name
Type: string
The name of the function as to be referenced in scripts.
source
Type: string
The script.
language
Type: string
The language of the script. Can be one of:
- csharp - Use the C# script engine
- jscript - Use the JScript script engine
- ruby - Use the IronRuby script engine (experimental support)
- python - Use the IronPython script engine (experimental support)
type
Type: string
The type of function. Can be one of :
assemblies
Type: string
The comma separated list of .NET assemblies to be loaded in order to support the script's compilation and execution.
|
Once loaded, these assemblies will remain loaded for the entire duration of the process execution. |
Return Value
Type: int
1 if the registration was successful, 0 otherwise.
Examples
Register and use a C# scalar function.
SELECT RegisterScriptFunction('StringReverse', '
using System;
class Script
{
public static void Main()
{
// Enter your code below
}
public object Execute(object[] parms)
{
object input = System.Runtime.CompilerServices.RuntimeHelpers.GetObjectValue(parms[0]);
string toReverse = Microsoft.VisualBasic.CompilerServices.Conversions.ToString(input);
return Microsoft.VisualBasic.Strings.StrReverse(toReverse);
}
public int GetParameterCount()
{
return 1;
}
public Type ReturnType()
{
return typeof(string);
}
}
', 'csharp', 'scalar', null)
SELECT StringReverse(XSElement.LocalName) from XSElement
Register and use a C# aggregate function.
SELECT RegisterScriptFunction('MyCount', '
using System;
class Script
{
public static void Main()
{
// Enter your code below
}
int count;
public void Init()
{
count = 0;
}
public void Accumulate(object value)
{
// do nothing
count ++;
}
public object Terminate()
{
return count;
}
public Type ReturnType()
{
return typeof(int);
}
}
', 'csharp', 'aggregate', null)
SELECT MyCount(*) from XSElement
Register and use a C# table function.
SELECT RegisterScriptFunction('MyTable', '
using System;
using System.Data;
class Script
{
public static void Main()
{
// Enter your code below
}
public DataTable Execute(object[] parms)
{
object input = System.Runtime.CompilerServices.RuntimeHelpers.GetObjectValue(parms[0]);
string toSplit = Microsoft.VisualBasic.CompilerServices.Conversions.ToString(input);
DataTable result = new DataTable("MyTable");
result.Columns.Add("Index", typeof(int));
result.Columns.Add("Value", typeof(string));
int i = 0;
foreach(string s in toSplit.Split(" "))
{
result.Rows.Add(new Object[] {i++, s});
}
return result;
}
public int GetParameterCount()
{
return 1;
}
}
', 'csharp', 'table', null)
SELECT *
FROM MyTable("The fox jumped over the other fence.")
UnregisterScriptFunction
Unregisters a user defined function.
Syntax
UnregisterScriptFunction ( name )
Arguments
name
Type: string
The name of the function to unregister.
|
If the function was executed once, any assembly loaded by the script will remain loaded even after the function was unregistered. |
Return Value
Type: int
1 if the unregistration was successful, 0 otherwise.
Example
SELECT UnregisterScriptFunction('MyTable');