How to test tsql variable contents as expression [duplicate]
Possible Duplica开发者_运维知识库te:
How to execute mathematical expression stored in a varchar variable
Using pure TSQL, is it possible to evaluate an expression defined to a variable?
Here is a simple example, its only for proof in concept:
DECLARE @TestValue AS VARCHAR (2) = '7';
DECLARE @myRule AS VARCHAR (100) = 'case when (#val#>0 and #val#<10) then 1 else 0 end';
SET @myRule = replace(@myRule, '#val#', @TestValue);
PRINT @myRule;
-- case when (7>0 and 7<10) then 1 else 0 end
--How to evaluate the expression in @myRule for True/False?
--Based on How to execute mathematical expression stored in a varchar variable
DECLARE @out AS BIT, @sql AS NVARCHAR (4000);
SET @sql = N'SELECT @out = ' + @myRule;
EXECUTE sp_executesql @sql, N'@out bit OUTPUT', @out OUTPUT;
PRINT 'sp_executesql=' + CAST (@out AS VARCHAR (10));
--sp_executesql=1
I've tested this approach and it seems to be sound. Not sure if there are performance considerations, but for now I'll mark as answered.
精彩评论