2020 Insight Weekly Tip: Call SQL Function from Formula

  • September 22, 2015 at 10:41 am #51083
    Wolfgang Schnall
    2020 Expert

    inSight uses a scripting language in formulas in many places such as order configuration and Bill Of Material processing.

    It is possible to call a SQL function from within such a formula. This may be helpful if there is business logic already stored outside of core insight functionality that needs to be considered by the formula.

    To take advantage of this, you or your SQL Administrator first has to create a SQL function that is called with one or more parameters and returns a text string

    Here is an example:

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnBAS_utlGetEdgebandColour_XYZ]’) AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’))

    DROP FUNCTION dbo.fnBAS_utlGetEdgebandColour_XYZ

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE FUNCTION dbo.fnBAS_utlGetEdgebandColour_XYZ ( @InputFeature1 Nvarchar (50),  @InputFeature2 Nvarchar (50) )

    RETURNS  Nvarchar (50)

    AS

    BEGIN

    declare @EdgeColor Nvarchar (50)

    —- put real lookup from table here

    if @InputFeature1= ‘1002’

    set @EdgeColor = ‘RED’ +’_’ + @InputFeature2

    if @InputFeature1 = ‘1003’

    set @EdgeColor=’BLUE’ +’_’ + @InputFeature2

    RETURN @EdgeColor

    END

    GO

    SELECT dbo.fnBAS_utlGetEdgebandColour_XYZ (‘1003’ , ‘B’)

     

    To call this function in a formula you would use the following syntax:

    Var A: String;

    Var B: String;

    A := [Feature1.Code];

    B := [Feature2.Code];

    Result := sql(‘dbo.fnBAS_utlGetEdgebandColour_XYZ’,A + ‘|’ + B);

     

You must be logged in to reply to this topic.

Share this Post