Select with several Procedures

You can not do select with procedures .

What you can do is reverse or replace the procedures with Function

Here are some examples to show you how to do. You can use stored procedure or table-valued function.

Scalar function is not suitable because it returns only one result and three averages are required. Also scalar functions are very inefficient.

USE AdventureWorks2016CTP3;
GO

-- Exemplo 1: procedure que devolve os resultados por OUT parameters
CREATE PROCEDURE dbo.up_Medias1
(
    @PrimeiroDoMes date,
    @MediaSubTotal money OUT,
    @MediaTaxAmt money OUT,
    @MediaFreight money OUT
)
AS

IF DATEPART(DAY, @PrimeiroDoMes) <> 1
    RAISERROR(N'@PrimeiroDoMes dia deve ser 1.', 12, 1);

SELECT @MediaSubTotal = AVG(SubTotal), @MediaTaxAmt = AVG(TaxAmt), @MediaFreight = AVG(Freight)
    FROM Sales.SalesOrderHeader
    WHERE OrderDate BETWEEN @PrimeiroDoMes AND EOMONTH(@PrimeiroDoMes);
GO

-- Testar o procedure
DECLARE @PrimDoMes date = N'2014-01-01';
DECLARE @MedSubTotal money;
DECLARE @MedTaxAmt money;
DECLARE @MedFreight money;

EXEC dbo.up_Medias1 @PrimDoMes, @MedSubTotal OUTPUT, @MedTaxAmt OUTPUT, @MedFreight OUTPUT;

-- Apresentar os resultados
SELECT @MedSubTotal AS 'Media SubTotal', @MedTaxAmt AS 'Media TaxAmt', @MedFreight AS 'Media Freight';

--Exemplo2:procedurequedevolveosresultadosporresultsetCREATEPROCEDUREdbo.up_Medias2(@PrimeiroDoMesdate)ASIFDATEPART(DAY,@PrimeiroDoMes)<>1RAISERROR(N'@PrimeiroDoMesdiadeveser1.',12,1);SELECTAVG(SubTotal)AS'MediaSubTotal',AVG(TaxAmt)AS'MediaTaxAmt',AVG(Freight)AS'MediaFreight'FROMSales.SalesOrderHeaderWHEREOrderDateBETWEEN@PrimeiroDoMesANDEOMONTH(@PrimeiroDoMes);GO--TestaroprocedureDECLARE@PrimDoMesdate=N'2014-01-01';EXECdbo.up_Medias2@PrimDoMes;

--Exemplo3:procedurequedevolvemediaspor12mesesCREATEPROCEDUREdbo.up_Medias3(@Anoint)ASSELECTYEAR(OrderDate)AS'Ano',MONTH(OrderDate)AS'Mês',AVG(SubTotal)AS'MediaSubTotal',AVG(TaxAmt)AS'MediaTaxAmt',AVG(Freight)AS'MediaFreight'FROMSales.SalesOrderHeaderWHEREOrderDateBETWEENDATEFROMPARTS(@Ano,1,1)ANDDATEFROMPARTS(@Ano,12,31)GROUPBYYEAR(OrderDate),MONTH(OrderDate)ORDERBYAno,Mês;GO--TestaroprocedureDECLARE@Anoint=2013;EXECdbo.up_Medias3@Ano;GO

--Exemplo4:functionquedevolvemediaspor12mesesCREATEFUNCTIONdbo.fn_Medias4(@Anoint)RETURNSTABLEASRETURN(SELECTYEAR(OrderDate)AS'Ano',MONTH(OrderDate)AS'Mês',AVG(SubTotal)AS'MediaSubTotal',AVG(TaxAmt)AS'MediaTaxAmt',AVG(Freight)AS'MediaFreight'FROMSales.SalesOrderHeaderWHEREOrderDateBETWEENDATEFROMPARTS(@Ano,1,1)ANDDATEFROMPARTS(@Ano,12,31)GROUPBYYEAR(OrderDate),MONTH(OrderDate));GO--TestarofunctionSELECT*FROMdbo.fn_Medias4(2013)ORDERBYAno,Mês;GO

CoDoRNeX, we normally use functions when we want to return some data to be used in a DDL Script, due to this nature a function can not insert, update, or erase data.

If your procedure does not modify any data from the database, then it should be easy to convete the same for a procedure, and as you said your procedure returns three values, then the best thing to do is to make a table valued-function .

Let's take for example the following function that returns three columns:

CREATE FUNCTION dbo.CalcMedia(@mes int) 
RETURNS TABLE 
AS
RETURN
(
    SELECT 1 AS Media1, 2 AS Media2, 3 AS Media3
)

So you could call it like this:

SELECT 
    Mes1.Media1, Mes1.Media2, Mes1.Media3,
    Mes2.Media1, Mes2.Media2, Mes2.Media3,
    Mes3.Media1, Mes3.Media2, Mes3.Media3,
    Mes4.Media1, Mes4.Media2, Mes4.Media3,
    Mes5.Media1, Mes5.Media2, Mes5.Media3,
    Mes6.Media1, Mes6.Media2, Mes6.Media3,
    Mes7.Media1, Mes7.Media2, Mes7.Media3,
    Mes8.Media1, Mes8.Media2, Mes8.Media3,
    Mes9.Media1, Mes9.Media2, Mes9.Media3,
    Mes10.Media1, Mes10.Media2, Mes10.Media3,
    Mes11.Media1, Mes11.Media2, Mes11.Media3,
    Mes12.Media1, Mes12.Media2, Mes12.Media3
FROM 
    dbo.CalcMedia(1) AS Mes1,
    dbo.CalcMedia(2) AS Mes2,
    dbo.CalcMedia(3) AS Mes3,
    dbo.CalcMedia(4) AS Mes4,
    dbo.CalcMedia(5) AS Mes5,
    dbo.CalcMedia(6) AS Mes6,
    dbo.CalcMedia(7) AS Mes7,
    dbo.CalcMedia(8) AS Mes8,
    dbo.CalcMedia(9) AS Mes9,
    dbo.CalcMedia(10) AS Mes10,
    dbo.CalcMedia(11) AS Mes11,
    dbo.CalcMedia(12) AS Mes12

But if you really need to run the procedures, after all you may need to insert some data while executing the Media calculation.

In this case you can insert the function return into a temporary table, so let's take the following store procedure as the base:

CREATE PROCEDURE dbo.CalcMedia @mes int    
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 1 AS Media1, 2 AS Media2, 3 AS Media3
END

Then do this:

DECLARE @medias AS TABLE (
    MesID int primary key identity,
    Media1 decimal(12, 2) not null,
    Media2 decimal(12, 2) not null,
    Media3 decimal(12, 2) not null
)

INSERT INTO @medias EXEC dbo.CalcMedia @mes = 1
INSERT INTO @medias EXEC dbo.CalcMedia @mes = 2
INSERT INTO @medias EXEC dbo.CalcMedia @mes = 3
INSERT INTO @medias EXEC dbo.CalcMedia @mes = 4
INSERT INTO @medias EXEC dbo.CalcMedia @mes = 5
INSERT INTO @medias EXEC dbo.CalcMedia @mes = 6
INSERT INTO @medias EXEC dbo.CalcMedia @mes = 7
INSERT INTO @medias EXEC dbo.CalcMedia @mes = 8
INSERT INTO @medias EXEC dbo.CalcMedia @mes = 9
INSERT INTO @medias EXEC dbo.CalcMedia @mes = 10
INSERT INTO @medias EXEC dbo.CalcMedia @mes = 11
INSERT INTO @medias EXEC dbo.CalcMedia @mes = 12

SELECT 
    Mes1.Media1, Mes1.Media2, Mes1.Media3,
    Mes2.Media1, Mes2.Media2, Mes2.Media3,
    Mes3.Media1, Mes3.Media2, Mes3.Media3,
    Mes4.Media1, Mes4.Media2, Mes4.Media3,
    Mes5.Media1, Mes5.Media2, Mes5.Media3,
    Mes6.Media1, Mes6.Media2, Mes6.Media3,
    Mes7.Media1, Mes7.Media2, Mes7.Media3,
    Mes8.Media1, Mes8.Media2, Mes8.Media3,
    Mes9.Media1, Mes9.Media2, Mes9.Media3,
    Mes10.Media1, Mes10.Media2, Mes10.Media3,
    Mes11.Media1, Mes11.Media2, Mes11.Media3,
    Mes12.Media1, Mes12.Media2, Mes12.Media3
FROM 
    @medias AS Mes1,
    @medias AS Mes2,
    @medias AS Mes3,
    @medias AS Mes4,
    @medias AS Mes5,
    @medias AS Mes6,
    @medias AS Mes7,
    @medias AS Mes8,
    @medias AS Mes9,
    @medias AS Mes10,
    @medias AS Mes11,
    @medias AS Mes12
WHERE 
    Mes1.MesID = 1 AND
    Mes2.MesID = 2 AND
    Mes3.MesID = 3 AND
    Mes4.MesID = 4 AND
    Mes5.MesID = 5 AND
    Mes6.MesID = 6 AND
    Mes7.MesID = 7 AND
    Mes8.MesID = 8 AND
    Mes9.MesID = 9 AND
    Mes10.MesID = 10 AND
    Mes11.MesID = 11 AND
    Mes12.MesID = 12