Evaluate expression and store the result

Question

Post reply

Evaluate expression and store the result

misstryguy

Ten Centuries

Points: 1052
More actions
July 26, 2012 at 12:24 am

#264688

Hello Experts,
I have an expression stored as string in one column. I like to evaluate the expression and store the result
as 1 or 0 in another column. I like to do this bulk update. Please give some suggestion.
CREATE TABLE [dbo].[tbl_expression](
[id1] [int] NULL,
[exp1] [varchar](50) NULL,
[result] [bit] NULL
insert into tbl_expression values(1,'30 > 50',Null)
insert into tbl_expression values(2,'70 > 50',Null)
insert into tbl_expression values(3,'20 = 20',Null)
expected result
1,'30 > 50',0
2,'70 > 50',1
3,'20 = 20',1
I tried doing this for single record.
declare @statment nvarchar(100)
declare @param nvarchar(100)
set @statment = '30 > 50'
declare @result bit
set @statment = 'select @result1= Case when ' + @statment + ' then 1 else 0 end'
set @param = '@result1 bit output'
exec sp_executesql @statment,@param,@result1=@result output
print @result
I appreciate your inputs.
Thanks
Kris

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sivaj2k Ten Centuries Points: 1188 More actions · Answer 1

Hi,

Please check the following code. Little bit lengthy procedure.

declare @tbl_expression TABLE

(

[id1] [int] NULL,

[exp1] [varchar](50) NULL,

[result] [bit] NULL)

insert into @tbl_expression values(1,'30 > 50',Null)

insert into @tbl_expression values(2,'70 > 50',Null)

insert into @tbl_expression values(3,'20 = 20',Null)

declare @test_tab table (id1 int,query varchar(max))

declare @test_tab1 table (result int)

insert into @test_tab

select id1,'select '+exp1 from @tbl_expression

update @test_tab set query = REPLACE(query,'>','-')

update @test_tab set query = REPLACE(query,'<','-')

update @test_tab set query = REPLACE(query,'=','-')

declare @start int=1,@vquery varchar(max),@result int

while @start <= (select COUNT(*) from @test_tab)

begin

select @vquery = query from @test_tab where id1 = @start

insert into @test_tab1 exec (@vquery)

update @tbl_expression set result = case when (select result from @test_tab1) <0 then 0 else 1 end

where id1 = @start

set @start = @start + 1

delete from @test_tab1

end

select * from @tbl_expression

Regards

Siva Kumar J.

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

Here's a bit less of a wordy solution.

First, your sample data: -

--Create your sample data

SELECT id1,exp1,result

INTO #tbl_expression

FROM (VALUES(1,'30 > 50',Null),(2,'70 > 50',Null),(3,'20 = 20',Null)

)a(id1,exp1,result);

Now on to the solution!

--Actual solution

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = STUFF((SELECT CHAR(13) + CHAR(10) + 'UPDATE #tbl_expression SET result = CASE WHEN ' + exp1 + ' THEN 1 ELSE 0 END WHERE result IS NULL AND id1 = ' + CAST(id1 AS VARCHAR(6)) + ';'

FROM #tbl_expression

WHERE result IS NULL

FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'');

EXECUTE sp_executesql @sql;

Results in: -

id1 exp1 result

----------- ------- -----------

1 30 > 50 0

2 70 > 50 1

3 20 = 20 1

You may want to consider adding some validation in there.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

misstryguy Ten Centuries Points: 1052 More actions · Answer 3

Thanks for putting it together.

Is there a way to use bulk update as this table will have huge volume.

Thanks

Kris

misstryguy Ten Centuries Points: 1052 More actions · Answer 4

Cadavre,

Your solution works great. Thanks a lot and I really appreciate it.

Thanks

Kris