How to evaluate a mathematical expression saved in nvarchar variable in SQL Server

i want to evaluate a mathematical expression saved in a variable in sql server

i google this a lot and found 3 solution but not applicable in my scenario

1- this solution cannot be executed inside a function but i need it inside a function

declare @expression nvarchar(max)
set @expression = '2*3*100'

declare @sql nvarchar(max)
set @sql = 'select @result = ' + @expression

declare @result int
exec sp_executesql @sql, N'@result int output', @result = @result out

select @result

2- this cannot be saved into a variable but i need to store the result into a variable

DECLARE @LocalVariable VARCHAR(32);
SET @LocalVariable = '2*3*100';
EXEC('SELECT ' + @LocalVariable);

3- the last solution i found gives me a error

DECLARE @x xml 
DECLARE @v decimal(20,4) 
SET @x = '' 
DECLARE @calculatedDataString nvarchar(1000) = '(1 div 100)*((118 div 100)*300.000000)' 
SET @v= @x.value('sql:variable("@calculatedDataString")', 'decimal(20,4)') 
SELECT @v

the error is

Msg 8114, Level 16, State 5, Line 5
Error converting data type nvarchar to numeric.

please advice

Solution

Perhaps this may help.

The following will evaluate a series of expressions, save the results to a #temp table. From there you can store the individual results into a variable

This is a dramatically scaled down version. The full one was built for macro substituions (i.e. calculate a series or Finanancial Ratios for multiple datasets)

If you provide a more robust USE CASE, perhaps I can help further

Example

Declare @Expression table (ID int,Expression varchar(max))
Insert Into @Expression values
 (1,'(1/100.0)*((118/100.0)*300.00000)')           -- Simple Calculation
,(2,'datediff(DD,''2016-07-29'',GetDate())')       -- System Functions
,(3,'(Select max(name) from master..spt_values)')  -- Select Value From Table
,(4,'convert(date,GetDate())')                     -- Get Today's Date


IF OBJECT_ID(N'tempdb..#Results') IS NOT NULL
BEGIN
    DROP TABLE #Results
END
Create table #Results (ID int,Value varchar(max))

Declare @SQL varchar(max)=''
Select  @SQL = @SQL+concat(',(',ID,',cast(',Expression,' as varchar(max)))') From @Expression 
Select  @SQL = 'Insert Into #Results Select * From ('+Stuff(@SQL,1,1,'values')+') N(ID,Value)'
Exec(@SQL)

Select * From #Results

Declare @Var decimal(10,4) = (Select Value From #Results where ID=1)
Select @Var  -- 3.5400

Temp Table

ID  Value
1   3.54000000000000000
2   243
3   YES OR NO
4   2017-03-29