return value from EXEC - urgent !

[fixthebug2003]

Hi,
I have a dynamically built SQL String in my Stored Procedure.
To execure it I use
EXEC(@v_sql_String)

The v_sql_string is a SELECT statement that returns a count [select count(*) from table1 where...]

is it not possible to get this count into a variable in the stored Procedure like this
SELECT @v_count=EXEC(@v_sql_String)

if yes let me know the exact syntax.
Thanks,
Fixthebug2003

 

[vidru]

Not a very elegant solution, but you could put your result into a temp table, then set the variable...
[/code]
create table #v_count(
v_count int)

insert #v_count EXEC(@v_sql_String)

select @v_count = v_count from #v_count
[/code]
-dave

 

[royjimenez]

i am sending u some code, i hope it helps u.

************************************************

SELECT @pub_qualified_name=QUOTENAME(@subscriber_db)+'.'+QUOTENAME(@tableowner)+'.'+QUOTENAME(@tablename)
SELECT @sub_qualified_name=QUOTENAME(@subscriber_db)+'.'+QUOTENAME(@tableowner)+'.'+QUOTENAME(@tablename)

-- Obtener los datos de Publicador
select @sqlstr = "SELECT SALDO_INI, SALDO_ACT FROM " + @pub_qualified_name + " where ROWGUIDCOL = '" + convert(varchar(36),@rowguid) + "'"
select @sqlstr = "DECLARE lcRepl CURSOR GLOBAL FAST_FORWARD FOR " + @sqlstr
execute ( @sqlstr )
if @@error <> 0 goto FALLO
open lcRepl
fetch next from lcRepl into @mon_salini, @mon_salfin
close lcRepl
deallocate lcRepl

-- Recuperar los datos del Subscriptor
select @sqlstr = 'SELECT * FROM ' + @sub_qualified_name + &quot; where ROWGUIDCOL = '&quot; + convert(varchar(36),@rowguid) + &quot;'&quot;
select @sqlstr = &quot;select COD_EMPRESA, ID_CUENTA, FEC_CIERRE from openquery(&quot; + QUOTENAME(@subscriber) + ',&quot;' + @sqlstr + '&quot'
select @sqlstr = 'DECLARE lcReplSaldos CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlstr
execute ( @sqlstr )
if @@error <> 0 goto FALLO
open lcReplSaldos
fetch next from lcReplSaldos into @empresa, @cuenta, @fcierre
close lcReplSaldos
deallocate lcReplSaldos

select @finicio = convert(datetime, convert(char(4), datepart(year, @fcierre)) + &quot;-&quot; + convert(varchar(2), datepart(month, @fcierre)) + &quot;-&quot; + convert(varchar(2),datepart(day, @fcierre)))
select @ffinal = dateadd(day, 1, @finicio)

-- Obtener el Detalle de Movimientos
select @sqlstr = QUOTENAME(@subscriber_db)+'.'+QUOTENAME(@tableowner)+'.'+QUOTENAME(@tablename + '_DESGLOSE')
select @sqlstr = &quot;select * from &quot; + @sqlstr + &quot; where COD_EMPRESA = '&quot; + @empresa + &quot;' and ID_CUENTA = &quot; + convert(varchar(10), @cuenta) + &quot; and FEC_CIERRE >= '&quot; + convert(varchar(30), @finicio) + &quot;' and FEC_CIERRE < '&quot; + convert(varchar(30), @ffinal) + &quot;'&quot;
select @sqlstr = 'select COD_EMPRESA, ID_CUENTA, FEC_CIERRE, sum(SALDO_INI), sum(SALDO_ACT) from openquery(' + QUOTENAME(@subscriber) + ',&quot;' + @sqlstr + '&quot group by COD_EMPRESA, ID_CUENTA, FEC_CIERRE'
select @sqlstr = &quot;DECLARE lcReplSaldosDet CURSOR GLOBAL FAST_FORWARD FOR &quot; + @sqlstr
execute ( @sqlstr )
if @@error <> 0 goto FALLO
open lcReplSaldosDet
fetch next from lcReplSaldosDet into @empresa, @cuenta, @fcierre, @mon_salini_sub, @mon_salfin_sub
close lcReplSaldosDet
deallocate lcReplSaldosDet

-- Calcular el Monto de Desfase
select @mon_salini = isnull(@mon_salini,0) + isnull(@mon_salini_sub,0)
select @mon_salfin = isnull(@mon_salfin,0) + isnull(@mon_salfin_sub,0)

...

*************************************************


The life is too short to cry and long enough to try it... God bless us.