How to execute mathematical expression stored in a varchar variable

Question

I have a variable in my database function:

@LocalVariable = '2*3*100'

I want to get the result by executing the expression in another variable. May any one advise on how to execute the expression? Also, I want to do it in a database function (not in a stored procedure). The result I expect is 600.

Answer 1

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

To get it into a variable:

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

DECLARE @out INT, @sql NVARCHAR(4000);
SET @sql = N'SELECT @out = ' + @LocalVariable;

EXEC sp_executesql @sql, N'@out INT OUTPUT', @out OUTPUT;

PRINT @out;

However you can't do this in a function, because you can't use EXEC, sp_executesql etc. in a function, sorry.