Stored Procedure In Select Statement

How do I run a stored procedure in a SELECT statement ? For example SELECT () A, () B I want to run or replace SQL CODE with predefined store

Solution 1:

The closest I know of is insert ... exec, like:

declare @t1 table (col1 int, col2 varchar(50))
insert @t1 exec ProcA

declare @t2 table (col1 int, col2 varchar(50))
insert @t2 exec ProcB

select  t1.col1
,       t1.col2
,       t2.col1
,       t2.col2
from    @t1 t1
cross join
        @t2 t2

The table definition must be exactly the same as the result set of the stored procedure. Missing columns or slightly different definitions will give an error.