I need to set an int value to @i as follows - except via dynamic sql (unless there is another way):
create table #tmp1(fld1 int, fld2 varchar(10))
insert into #tmp1
select 1, 'aa' union all
select 2, 'bb' union all
select 3, 'aa' union all
select 4, 'bb' union all
select 5, 'cc' union all
select 6, 'cc' union all
select 7, 'dd'
declare @s varchar(10), @t varchar(200), @i int
set @s = 'aa','bb','c'
--this works fine
--set @t = 'select sum(fld1) from #tmp1 where fld2 in ('' + @s + '')'
--this not working -- how to set value to @i?
set @t = 'select ' + @i + ' = cast(sum(fld1) as int) from #tmp1 where fld2 in ('' + @s + '')'
@s is a param that gets passed into a proc as 'aa','bb','c','d' -- so I need to use dynamic sql. Is there another way I could get a value into @i? I guess I could Insert the sum result to a temp table and then retrieve the value into @i
from #tmp2. But is there a more direct way to do this?
View Complete Post