# Calculate Expression in Sql Server RSS

Last post Feb 12, 2019 12:34 AM by Brando ZWZ

• salman beher...

All-Star

21479 Points

5971 Posts

### Calculate Expression in Sql Server

Feb 08, 2019 10:44 AM|salman behera|LINK

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 PAYID VALUE E001 1 1000 E002 1 2000

how can i calculate ID 2 formula value of empid E001. calculate like

parameter1=basic

and basic of E001=1000

so as per formula if parameter1>4000 then Parameter5 (may be value 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

Sincerely,
Salman
• Brando ZWZ

Star

9821 Points

3120 Posts

### Re: Calculate Expression in Sql Server

Feb 12, 2019 12:34 AM|Brando ZWZ|LINK

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

MSDN Community Support