The SQL Functions View enables you to create, edit and view SQL Functions for various different types within the application. Each one has an associated .net clr assembly containing the corresponding clr function that can be called from within sql.
SQL Function Properties
SQL Functions have the following properties:
Name
Description
[Key] [Required] Name
The unique name for this function that will be used in sql to refer to it.
Type
The type of clr user defined function to be imported:
Aggregate
Enabled
Indicates if this sql function is enabled and should be imported into the database.
Assembly Name
The name of the assembly to use when importing this clr sql assembly into the database
External Name
The name of the class in the assembly to use to call the aggregate methods on for calculating the result in sql
Create SQL
The actual sql following the Create Aggregate sql command defining the schema of your user defined clr function holding the inputs and output. Such as :
CREATE AGGREGATE WeightedAvg (@value int, @weight int) RETURNS int
As illustrated in the SQL Server CLR User Defined Functions here.
Is System
Indicates if this is a system SQL Function that should not be edited by the user.
Is Public
Indicates if this sql function is public or only visible to the current user.
Version
The current version of this SQL Function
Description
A user friendly description of the calculation performed by this SQL F
Once created you can use the function itself in a dataset by calling the aggregate function with its Name and required parameters, and setting the Has Custom Aggregate field to true. The system will then include your custom calculation automatically when running this sql query according to your specified grouping levels. All automatically, and most importantly all from standard sql as fast and efficient as possible. This brings the power of custom calculations to your query tools.
A SQL Functions has the standard set of commands and functions when being viewed from a grid. See our standard grid functionality.
When viewed from the grid a Data Template has the following custom commands:
Change CLR Assembly: This allows you to select the .net dll assembly that contains your sql function into the LemonEdge platform.
Save Template: This allows you to download the .net dll assembly for this SQL Function into your local system.
