Execute store procedure like a "table" for SELECT operator (MS SQL SERVER)

Question

Is it possible to execute store procedure like a "table" for SELECT operator (MS SQL SERVER)?

Something like

SELECT TotalSum FROM exec MyStoreProcedure '2011/11/01', '2011/11/01'   

I mean somehow integrate it into the SELECT operator?

Thank you!

---

Thanks guys!

The solution what I did is based on your answers:

declare @result table (f1 varchar(20),f2 varchar(20), CodProducto int, NomProducto varchar(1000), Costo decimal, Cantidat int, Total decimal) INSERT INTO @result exec  MyStoreProcedure '20111201', '20111201' select * from @result 

Answer 1

I supposed your proc returns several columns and you just want one, right?

small workaround is to add the result of the proc to a table variable and then select from it

create proc proc1 as select 1 as one, 2 as two  declare @result table (one int, two int)  insert into @result exec proc1  select one from @result 

Answer 2

This would be better as a function rather than a stored procedure.

create function dbo.TestTable (@var1 bit) returns table AS RETURN ( select *     from INFORMATION_SCHEMA.TABLES     where @var1 = 1 );   select * from dbo.TestTable(1)