background
I have a stored procedure in a database in SQL Server 2012 1 that gets the average of some values. This stored procedure receives two (2) values of type int as parameters.
Example:
- OfficialId
- ProcessId
1 It is the version of the database manager that I have on the development computer, but it is possible that this database is installed in a different version.
Problem
Since the stored procedure must 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 castellated procedure that calculates the average according to the employee and specified process; returning two columns:
---------------------------
| Promedio 1 | Promedio 2 |
---------------------------
The results I expect to get 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 |
---------------------------------------
Searching other questions and answers on Stack Overflow, I found this answer where basically a temporary table is created to store the results of the stored procedure, but I can't adjust the query so that, when iterating through all the officers (of the Officers table) , is executed and saved in the temporary table.
The errors I have are syntax errors 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, a 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, by logic, I could first query the "Officers" table and make a loop to call the stored procedure and process everything "by back-end", without However, in the project in which I am involved, all the logic is intended to be applied in stored procedures.
It is not possible to call a stored procedure directly from a query, but what you can do is call it through a function, that is, instead of
SP_PromedioReporte
being a stored procedure, it is a function, for which it would be something like this:With this, you can now send it to be called in your query directly with
dbo.FN_PromedioReporte(temp.IdFuncionario, temp.IdProceso)
:Here you can see thedemostración
At least as far as I know, the only way to solve it is by means of a cursor, it's not ideal and we should always look for better alternatives, but hey... it works. Your example would look something like this: