Execute store procedure like a "table" for SELECT operator (MS SQL SERVER)
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 asselect 1 as one, 2 as twodeclare @result table (one int, two int)insert into @resultexec proc1select one from @result
This would be better as a function rather than a stored procedure.
create function dbo.TestTable(@var1 bit)returns tableASRETURN( select * from INFORMATION_SCHEMA.TABLES where @var1 = 1);select * fromdbo.TestTable(1)
Not directly (or without altering the stored procedure to be a table-valued function).
But you could do this:
INSERT INTO SomeTempTableWithSchemaMatchingTheSproc (...)EXEC MyStoredProcedure SELECT * FROM SomeTempTableWithSchemaMatchingTheSproc SQL Server 2005 onwards, you can also use a table variable.