Parameter returned in a execute

[pap]

Hello,

How can I pass the value of the count(*) to outside the execute. I need the value to continue with the sp

set @sql= 'declare @res int select @res=count(*) from t
where tam=(select '+@posxx+' from sffpoxx0 where ponbr='+@Col001+' and poodn='+@Col002+' and pocat='+@Col007+')'

???????? set @result=exec (@sql) ????????????? Something like this


Thanks
Paulo

[YuckFou]

1. capture result into a table
create table #res (res int)
set @sql= 'select count(*) from t
where tam=(select '+@posxx+' from sffpoxx0 where ponbr='+@Col001+' and poodn='+@Col002+' and pocat='+@Col007+')'
insert into #res exec(@sql)

2. or through cursor into variable
set @sql= 'declare cr insensitive cursor for select count(*) from t
where tam=(select '+@posxx+' from sffpoxx0 where ponbr='+@Col001+' and poodn='+@Col002+' and pocat='+@Col007+')'
exec(@sql)
open cr
fetch next from cr into @res
deallocate cr

[pap]

Thank you very much YuckFou,

The first one runs okay

I tried also the second option but it doesn't run so well, because I need to declare also the cursor outside the execute (in the sp).

No matter, the first one runs okay.

Now tell me just one think, this is the only way that I can get the data from a execute function ?

Thanks for your time
Paulo

[YuckFou]

2. you don't need to declare cursor outside...
declare @sql varchar(100), @res int
set @sql= 'declare cr insensitive cursor for select -1'
exec(@sql)
open cr
fetch next from cr into @res
deallocate cr
select @res
--returns -1

3. another way is through procedure...
declare @sql varchar(100), @res int
set @sql= 'create procedure dbo.#getres as return -1'
exec(@sql)
exec @res = dbo.#getres
drop procedure dbo.#getres
select @res
--returns -1