locked
Calculate Expression in Sql Server RRS feed

  • Question

  • User-448512826 posted

    Hi,

    i have two table like below

    ID PAYNAME PAYTYPE FORMULA CALCULATEID RATE
    1 BASIC LUM NULL parameter1 NULL
    2 ALLOWANCE FOR if ({parameter1}>1000,{parameter5},{parameter1}/100) parameter2 NULL
    3 HRA LUM NULL parameter3 NULL
    4 ESI MAP   parameter4 10
    5 FD LUM   parameter5 NULL
    EMPID <g class="gr_ gr_427 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace gr-progress" id="427" data-gr-id="427">PAYID</g> VALUE
    E001 1 1000
    E002 1 2000

    how can i calculate ID 2 formula value of <g class="gr_ gr_428 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="428" data-gr-id="428">empid</g> E001. calculate like

    parameter1=basic

    and basic of E001=1000

    so as per formula if parameter1>4000 then Parameter5 (may <g class="gr_ gr_2501 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="2501" data-gr-id="2501">be value</g> is 200 of parameter5)

    so for E001 basic is 1000 and it is false against if condition and value should be {parameter1}/100 like 1000/100=10.

    so is it possible to execute if and else condition in a column and calculate its value??

    Thanks 

    Friday, February 8, 2019 10:44 AM

All replies

  • User283571144 posted

    Hi salman behera,

    According to your description, I suggest you could consider using SQL Server Eval package to achieve your requirement.

    SQL Eval.NET is a complete solution which, not only lets you evaluate dynamic arithmetic expression, but lets you use the full C# language directly in T-SQL stored procedures, functions and triggers.

    Some example:

    DECLARE @items TABLE (Quantity INT, Price MONEY)
    
    INSERT  INTO @items
    VALUES  ( 2, 10 ),
            ( 9, 6 ),
            ( 15, 2 ),
            ( 6, 0 ),
            ( 84, 5 )
    
    DECLARE @customColumn SQLNET = SQLNET::New('(quantity * price).ToString("$#.00")')
    DECLARE @customFilter SQLNET = SQLNET::New('quantity > 3 && price > 0')
    
    -- Select_0: 9, 6.00, $54.00
    -- Select_1: 15, 2.00, $30.00
    -- Select_2: 84, 5.00, $420.00
    SELECT  * ,
            @customColumn.ValueInt('quantity', Quantity).Val('price', Price).EvalString()
    FROM    @items
    WHERE   @customFilter.ValueInt('quantity', Quantity).Val('price', Price).EvalBit() = 1

    Best Regards,

    Brando

    Tuesday, February 12, 2019 12:34 AM