CODEWITHSUNDEEP
t-sqlsql-server-2008
DanielD
DanielD

Reputation: 45

"EXEC(columnname)" for each row in a SELECT, and get return value

What would be the best solution, if i have a table like this:

Value   Formular
12      'SELECT Value+5 AS result'
15      'SELECT 4/Value+3 AS result'
16      'SELECT 5 AS result'

So, there are SQL-Statemants (as nvarchar(max)) in the Formular-Column. Now i need to calc for each row the result.

Current approach is a WHILE with cursor and handle each row for itself.

We need to use a temp-table for each row, because we were unable to find a better solution, how to get the result from the exec. Like:

declare @Value decimal(18,4);
create table #t(val decimal(18,4))
insert into #t exec(@formular)
select val from #t

We have >100'000 such rows, and this takes about ~2h do compute. (The formulas and values of course complexer, than in the example given)

Is there any possibility to get something like

SELECT Value, exec(Formular) as result FROM calctable

Thanks

EDIT:

Okay, sofar I found atleast a workaround for the EXEC() and temptable: 

declare @formular as nvarchar(max)
declare @r_value decimal(18,4);
set @formular='....'
set @formular='select @r_value = Value FROM (' + @formular + ')dt'
EXEC sp_executesql @formular, N'@r_value DECIMAL(18,4) OUTPUT', @r_value OUTPUT
UPDATE Result_Values SET [Value]=@r_value WHERE ID=@calc_id;

Upvotes: 1

Views: 766

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44326

This example can be executed:

Creating a table like your calctable, you can omit this part.

create table calctable(value int)
insert calctable values(1)
insert calctable values(3)

Populating a temp table, you can replace this with your own table.

declare @t table(value int, Formular varchar(50))

insert @t values(12,'Value+5 AS result' )
insert @t values(15,'4/Value+3 AS result')
insert @t values(16,'5 AS result')

This is the syntax you need:

declare @sql varchar(max)

select @sql = coalesce(@sql +' union all ', '') + 'select ' + cast(value as varchar(16)) +' value,'+formular+' from calctable' from @t
exec (@sql)

Result:

value  result
12     6
12     8
15     7
15     4
16     5
16     5

Upvotes: 2

Related Questions