Store stored procedure results in temporary table

Question

Store stored procedure results in temporary table

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

Background

I have a procedure stored in a database in SQL Server 2012 1 that averages some values. This stored procedure receives as parameters two (2) values of type int.

Example:

IdFuntionary
IdProcess

1 It is the version of the database manager that I have on the development team, but it is possible that this database is installed in a different version.

Problem

Since the stored procedure should also allow to obtain the average of all the officials, I would like to create a new procedure to execute it in this way:

Test query code:

-- Crear tabla temporal. Código adaptado de 
-- https://stackoverflow.com/a/654418/4092887
CREATE TABLE #tmpBus
(
   IDFUNCIONARIO NUMERIC (12, 0),
   NOMBRE VARCHAR(100),
   CAL_1 FLOAT,
   CAL_2 FLOAT
);

-- Usando CTE "Common Table Expressions", recorrer la tabla "Funcionarios"
-- e invocar el procedimiento almacenado para ir guardando los resultados
-- en la tabla temporal "#tmpBus".
WITH CTE_func
AS
(
    SELECT FUNC.IdFuncionario, FUNC.Nombre, 1 AS IdProceso
    FROM Funcionario AS FUNC
)
SELECT temp.Nombre, EXEC SP_PromedioReporte temp.IdFuncionario, temp.IdProceso
FROM CTE_func AS temp

-- Retornar los valores almacenados en la tabla temporal.
SELECT *
FROM #tmpBus

Where SP_PromedioReporte is the crenelated procedure that calculates the average according to the employee and process specified; returning two columns:

---------------------------
| Promedio 1 | Promedio 2 |
---------------------------

The results I hope to obtain are similar to this table:

---------------------------------------
| Nombre    | Promedio 1 | Promedio 2 |
---------------------------------------
|Alfonso    | 10         | 85         |
---------------------------------------
|María      | 12         | 38         |
---------------------------------------
|Jaime      | 16         | 63         |
---------------------------------------
|Rodrigo    | 20         | 69         |
---------------------------------------
|Laura      | 18         | 49         |
---------------------------------------

Looking at other questions and answers in Stack Overflow, I found this answer where basically a temporary table is created to store the results of the stored procedure, but I can not adjust the query so that when traversing all the officials (from the Officers table) , it is executed and saved in the temporary table.

The errors I have are of syntax and I admit that I have no idea how I can perform this functionality.

How can I generate a stored procedure that in turn calls another stored procedure "in this case, at SP_PromedioReporte " as explained in this question? see test query code: .

NOTE: For this case, I am looking for a solution purely in SQL Server, since, logically, I could first consult the table "Officials" and make a cycle to go calling the stored procedure and process everything "by back-end ", however, in the project in which I am involved, we want all logic to be applied in stored procedures.

sql sql-server

asked by Mauricio Arias Olave 18.04.2017 в 18:57

source

2 answers

0

At least up to where I know the only way to solve it is through a cursor, it is not ideal and you should always look for better alternatives, but hey ... it works. Your example would look something like this:

CREATE TABLE #tmpBus
(
   IDFUNCIONARIO NUMERIC (12, 0),
   NOMBRE VARCHAR(100),
   CAL_1 FLOAT,
   CAL_2 FLOAT
)

DECLARE _C CURSOR READ_ONLY FOR 
SELECT  IdFuncionario, 
        1 AS IdProceso
        FROM Funcionario 

DECLARE @IdFuncionario  NUMERIC (12, 0)
DECLARE @IdProceso  NUMERIC (12, 0)

OPEN _C

FETCH NEXT FROM _C INTO @IdFuncionario, @IdProceso
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        INSERT INTO #tmpBus(IDFUNCIONARIO, NOMBRE, CAL_1, CAL_2) 
        EXEC SP_PromedioReporte @IdFuncionario, @IdProceso
    END
    FETCH NEXT FROM _C INTO @IdFuncionario, @IdProceso
END

CLOSE _C
DEALLOCATE _C

SELECT  *
    FROM #tmpBus

answered by 19.04.2017 в 01:04

listview prints repeated values How to properly use the Bootstrap Cards

score 2 · Accepted Answer

It is not possible to send a stored procedure directly from a query, but what you can do is send it to call through a function, that is, instead of SP_PromedioReporte being a stored procedure, this is a function, for which it would look something like this:

CREATE FUNCTION dbo.FN_PromedioReporte (@IdFuncionario INT, @IdProceso INT)
RETURNS DECIMAL(18,6)
AS BEGIN
    DECLARE @resultado DECIMAL(18,6)

    --Código de ejemplo para regresar un resultado, yo puse una división para simular el valor de retorno
    --Aquí va la lógica de lo que anteriormente era un Stored Procedure
    SET @resultado = @IdFuncionario / @IdProceso

    RETURN @Resultado
END

With this, you can already send it to call in your query directly with dbo.FN_PromedioReporte(temp.IdFuncionario, temp.IdProceso) :

....
SELECT temp.Nombre, dbo.FN_PromedioReporte(temp.IdFuncionario, temp.IdProceso)
FROM CTE_func AS temp
....

Here you can see the demo