开发者

How to test tsql variable contents as expression [duplicate]

This question already has answers here: Closed 11 years ago.

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜