Evaluate And Execute IF Statement in Formula Editor

Posted Date: 04-Jun-2012	Category: SQL Server
Author: Ravindra Gaurana	Member Level: Gold	Points: 50

This article will help you to evaluate if statement in your formula. So application like having formula and we want to evaluate if statement dynamically in that scenario we can use this stored procedure to evaluate if expression. This will meet your following requirement Dynamically Evaluate If or Nested If statement,Expression Evaluation Parser,Formula Editor expression execution,Execution Service for Expression

Introduction

Problem : How to evaluate if statement or nested if statement or how we can evaluate particular expression given for calculation or evaluation. In c#.net it is vary difficult to evaluate expression having/with if statement.

Solution : We can use exiting functionality of Sql Server keyword Exec statement by little modification in it while evaluating the expression.

Step 1 Create Stored Procedure


CREATE procedure [dbo].[EvaluateExpression]
@Expression as varchar(1000),
@Result as varchar(500) output
as
BEGIN
	Declare @TempTable Table (VariableValue varchar(500))
	SET @Expression=(Select replace(@Expression,'==','='))        
	SET @Expression='if (0=1) Then 0 else ' + @Expression
	SET @Expression = replace(@Expression,'Then','Select')
	SET @Expression = replace(@Expression,'else','else Select')
	SET @Expression = replace(@Expression,'Select if','if')	
	Insert into @TempTable exec (@Expression);
	Select @Result=VariableValue from @TempTable;
END

Step 2 : Execute Stored Procedure


declare @v varchar(1000);
declare @formula as varchar(1000)
set @formula = 'IF(5=5) THEN IF(5=5) Then 10 ELSE 5'
exec [EvaluateExpression] @formula,@Result=@v output
select @v

Note : It will reduce efforts to write new evaluation engine in c# and but still we can pick the right things at right time as per our requirements.

Post Comment:

Notify me by email when others post comments to this article.

Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.

No HTML formatting and links to other web sites are allowed.

This is a strictly moderated site. Absolutely no spam allowed.

Name:	Sign In to fill automatically.
Email:	(Will not be published, but required to validate comment)
	Type the numbers and letters shown on the left.