Select with various Procedures

Question

Good Morning, How do I perform the select with several processes? I have a process that returns 3 measures of 1 month specific. Now I need to run this proc 12x in select. Type: SELECT (SP_JANEIRO), (SP_FEVEREIRO), (SP_MARCO)..... Anyone knows how I can do this?

Answer 1

You can’t do it select with procedures.

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

Answer 2

Here are some examples to show 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';

-- Exemplo 2: procedure que devolve os resultados por result set
CREATE PROCEDURE dbo.up_Medias2
(
    @PrimeiroDoMes date
)
AS

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

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

-- Testar o procedure
DECLARE @PrimDoMes date = N'2014-01-01';

EXEC dbo.up_Medias2 @PrimDoMes;

-- Exemplo 3: procedure que devolve medias por 12 meses
CREATE PROCEDURE dbo.up_Medias3
(
    @Ano int
)
AS

SELECT YEAR(OrderDate) AS 'Ano', MONTH(OrderDate) AS 'Mês', AVG(SubTotal) AS 'MediaSubTotal', AVG(TaxAmt) AS 'MediaTaxAmt', AVG(Freight) AS 'MediaFreight'
    FROM Sales.SalesOrderHeader
    WHERE OrderDate BETWEEN DATEFROMPARTS(@Ano, 1, 1) AND DATEFROMPARTS(@Ano, 12, 31)
    GROUP BY YEAR(OrderDate), MONTH(OrderDate)
    ORDER BY Ano, Mês;
GO

-- Testar o procedure
DECLARE @Ano int = 2013;

EXEC dbo.up_Medias3 @Ano;
GO

-- Exemplo 4: function que devolve medias por 12 meses
CREATE FUNCTION dbo.fn_Medias4
(
    @Ano int
)
RETURNS TABLE
AS
RETURN (SELECT YEAR(OrderDate) AS 'Ano', MONTH(OrderDate) AS 'Mês', AVG(SubTotal) AS 'MediaSubTotal', AVG(TaxAmt) AS 'MediaTaxAmt', AVG(Freight) AS 'MediaFreight'
    FROM Sales.SalesOrderHeader
    WHERE OrderDate BETWEEN DATEFROMPARTS(@Ano, 1, 1) AND DATEFROMPARTS(@Ano, 12, 31)
    GROUP BY YEAR(OrderDate), MONTH(OrderDate));
GO

-- Testar o function
SELECT *
    FROM dbo.fn_Medias4(2013)
    ORDER BY Ano, Mês;
GO

Answer 3

Codornex, we usually use functions when we want to return some data to be used in a DDL Script, due to this nature a function cannot insert, update, or erase data.

If your trial does not modify any database data, then it should be easy to check the same for a trial, and as you said your trial 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
)

Then you could call her as follows:

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 perform the procedures, after all you may need to insert some data while running does the Media calculation.

In this case you can insert the return of the function in a temporary table, then we will take the following store:

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

then do so:

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