SELECT against stored procedure SQL Server

Question

SELECT Val from storedp_Value within the query editor of SQL Server Management Studio, is this possible?

UPDATE

I tried to create a temp table but it didn't seem to work hence why I asked here.

CREATE TABLE #Result
(
batchno_seq_no int
)
INSERT #Result EXEC storedp_UPDATEBATCH
SELECT * from #Result
DROP TABLE #Result
RETURN

Stored Procedure UpdateBatch

delete from batchno_seq;
insert into batchno_seq default values;
select @batchno_seq= batchno_seq_no from batchno_seq
RETURN @batchno_seq

What am I doing wrong and how do I call it from the query window?

UPDATE #2

Ok, I'd appreciate help on this one, direction or anything - this is what I'm trying to achieve.

 select batchno_seq from (delete from batchno_seq;insert into batchno_seq default values;
 select *  from batchno_seq) BATCHNO 
 INTO TEMP_DW_EKSTICKER_CLASSIC

This is part of a larger select statement. Any help would be much appreciated. Essentially this SQL is broken as we've migrated for Oracle.

Answer 1

The best article (in my opinion) about all possible methods for sharing data between stored procedures in SQL Server you can find here: http://www.sommarskog.se/share_data.html