Calculate Expression in Sql Server

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 

Answer 1

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