Introduction

How to evaluate an arithmetic expression in SQL Server is a common subject. There are several reasons why an "Eval" function like JavaScript could be useful in SQL such as evaluating custom report field for a trusted user.

Multiple partial solutions exists like using "EXEC(Transact-SQL)" which is limited, cannot be used inside SELECT statement and lead to SQL Injection or using an homemade User-Defined Function which, most of time, fail at supporting simple operator priority and parenthesis.

So do a complex solution exists for SQL Server?

Solution

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.

Example


SQL Dynamic arithmetic operations and math functions

Problem

You need to evaluate a dynamic arithmetic operation specified by a trusted user or check a dynamic rule.

  • Dynamic report calculation field
  • Dynamic report query filter
  • Dynamic rule validation

Solution

Eval SQL.NET supports all C# operators including operators precedence and parenthesis.

Evaluating an expression is very fast and scalable. You can see performance 3-20x faster than User-Defined Function (UDF) and you can evaluate an expression as much as ONE MILLION times under a second.

Example


Dynamic SQL Switch Case, Condition and Expression

Problem

You need to evaluate and execute a dynamic SQL expression which requires more than basic arithmetic operators.

  • if/else
  • switch/case
  • try/catch

Solution

Eval SQL.NET is flexible and supports almost all C# keywords and features including:

  • Anonymous Type
  • Generic Type
  • Lambda Expression
  • LINQ

Example


SQL Eval Framework Class Library

Problem

You have a complex SQL and you know C# Syntax and C# Object could make this problem very easy.

  • Regex
  • DirectoryInfo / FileInfo
  • String.Format

Solution

Eval SQL.NET improve readability and maintainability over complex SQL. It supports all .NET framework class libraries (FCL) that are supported by SQL CLR Framework Libraries.

Example


Conclusion

Eval SQL.NET can really be seen in SQL Server as the function "eval()" equivalent of JavaScript. Unlike common solutions limited to very simple math expressions, Eval SQL.NET features go way beyond:

  • Access to C# Operators
  • Access to C# Keywords
  • Access to C# Objects

Getting better performance than User-Defined Function (UDF) and Table-Valued Function (TVF) is the Icing on the Cake!


Share your experience

  • What's features is missing?
  • Which library are you using and why?
  • What's your performance experience?