Stored procedure result to variable

[Rauland]

How would I assign the value of the result of executing a stored procedure to a variable.

I tried this:

exec @var = proc1

But this assigns the return value and not the result , to @var.

I believe the return value will return 0 if no errors occur.
Thanks

[Clanguage]

I have not tried it but maybe you could do

set @var = exec proc1

[Rauland]

No it didnt work,..

I tried this in the end it did work, I know I could also use output parameters.

DROP TABLE #temp_table
CREATE TABLE #temp_table (availability int)
INSERT INTO #temp_table exec SelectALLTest
SELECT availability FROM #temp_table

[Rauland]

Got the feeling there must be a better way,.... without using OUTPUT parameters,..

[brucevde]

It would depend on the stored procedure.

What are it's results? A single value, a resultset?
If the result is a single integer value just set the Return value of the sproc. It will then be loaded into your @var variable.

What have you got against OUTPUT parameters.

[Rauland]

Hi, it´s a resultset.
I do not have anything against output parameters,but wouldn´t like to modify an existing sp.

[riteshjain1982]

if it's result set than Inline Table-Values function will be the best option for you, by this way you can directly worked upon the result set retun by function without creating Temp Table.