passing values of dynamic select statements into a variable

[ma77]

A situation has arisen where I would like to be able to run the folloing kind of command.
I have to build the statement up in a string and execute it. I also have to pass the returned figure into a seperate variable.
Ideally I would like to be able to run :
select @value=exec(@SQLSTRING)

but this does not seem to be supported.

ANY IDEAS??
Cheers,
M@.

 

[dnascim]

It creates a temporary table and of an insert of the result from the stored procedure. Example:

create proc test
as
set nocount on
select au_lname from pubs..authors
go

create table #test
(
au_lname varchar(40) not null
)

insert #test
exec test

select * from #test

I expect to have helped.