KBBWrite KBBWrite - 1 year ago 108
SQL Question

How to execute mathematical expression stored in a varchar variable

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 Source

You could use sp_executesql:

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

Example at SE Data.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download