Boolean logic parser for SQL

Question

This is going to sound crazy but does anyone have techniques that would allow me to parse boolean logic strings in Sql Server 2005 without extraordinary/rediculous effort?

Here is an example: (SOMEVAR=4 OR SOMEVAR=5) AND (NOT OTHERVAR=Y)

I feel like recursion would help a lot if that were possible in Sql but I'm not really sure how to go about that sort of thing. If not, maybe there's a way to attach an external system to do the recursion for me? Don't worry, I'm not getting my hopes up.

Answer 1

Your best bet is to use CLR integration. This will make it easier to find or develop code that will solve this issue.

Answer 2

If you are receiving the logic strings as input from some process (web site or whatever), you could use them to build up and execute a dynamic sql string. You would have to be very careful about injection attacks with this approoach.

Answer 3

Judging from your comment that you need to know 'ultimately find out if the most outer statement ends up being true or false' then you don't need to parse the expression, you need to evaluate it. You can try with a sp_executesql:

...
declare @sql nvarchar(max);
declare @result bit;
-- consider @expression has the expression to be evaluated
-- eg. @expression = '(@SOMEVAR=4 OR @SOMEVAR=5) AND (NOT @OTHERVAR=''Y'')';
set @sql = N'set @result=case when ' + @expression + N' then 1 else 0 end;';
exec sp_executesql @sql, N'@SOMEVAR int, @OTHERVAR vachar(max), @result bit output', @SOMEVAR, @OTHERVAR, @result output;
SELECT @result;
...

While this does what you're interested (evaluate an expression), it has major problems:

• it is open to SQL Injection, the @expression must be trusted input
• @expression can only reference local variables you pass in during sp_executesql
• you need to pass in all local variable to sp_executesql, or at least all local variables that can appear in @expression
• is difficult to maintain as any new local variable has to be passed on to sp_executesql (both declared as parameter and actually added as parameter)
• any error in the @expression will be propagated up as an execution error (you can wrap in a BEGIN TRY/BEGIN CATCH to prevent this)

I don't know your specific context, but try to consider whether the evaluation is necessary. I don't say downright it isn't, since I myself had to deal with similar problems (expressions coming from configuration tables that needed to be evaluated).