CODEWITHSUNDEEP
sql-serversql-server-2008expression
Tomi7979
Tomi7979

Reputation: 53

MS sql 2008 r2 - evaluate an expression which is in a variable

I am using Ms sql server 2008 and I am store expessions in an nvarchar variable. I would like evaulate them as a normal expression.

Here is my example from the management studio:

DECLARE @exp nvarchar (50)
SET @exp ='(1&1)|1'
PRINT (1&0)|1
PRINT @exp

The expected result of the last line is 1.

I found this link on the technet site. (EvaluateAsExpression)

It uses some C# code which I don't know how to implement in the management studio. Or do you have other solution?

Thank, Tamas!

Upvotes: 1

Views: 956

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294277

EXEC can evaluate expressions, see SqlFiddle:

DECLARE @exp nvarchar (50)
SET @exp = N'1+2+3+4+5';
exec (N'SELECT ' + @exp);

I would advise against such though. If you want to use a safe expression evaluator, build one in CLR and integrate it as a SQLCLR function, see How to: Create and Run a CLR SQL Server User-Defined Function.

Capture value in variable:

DECLARE @exp nvarchar (50)
SET @exp = N'1+2+3+4+5';

declare @result int;
declare @sql nvarchar(max) = N'set @result = (' + @exp + ');';
exec sp_executesql @sql, N'@result int output', @result output;

select @result;

the usual caution has to be in place whenever executing dynamic SQL, this is a possible SQL injection attack vector so the input must be absolutely trusted. One more reason why a custom built parser in C# would be better/safer.

Upvotes: 1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

You can try creating a dynamic query and executing it:

DECLARE @exp nvarchar (50)

SET @exp ='(1&1)|1'
PRINT (1&0)|1

SET @exp = 'PRINT ' + @exp
EXEC sp_executesql @exp

This will actually create a new PRINT statement with the contents of @exp variable and execute it.

Upvotes: 0

Related Questions