How to call a stored procedure inside another

Question

How to call a stored procedure inside another

Navigation

#1 by (3 votes)

0

As I can solve this problem, I am trying to call a stored procedure inside another, this is the code that I have done so far, in the first image the stored procedure works (SP_SELECTINVENTARIO) in the second is the problem that I have , how can I perform the stored procedure.

this is the code

-- SELECCIONAR INVENTARIO
CREATE PROCEDURE SP_SELECTINVENTARIO
AS BEGIN
SET NOCOUNT ON; 
SELECT C.NAMECLIENTE AS NOMBREPROYECTO,C.NUMPARTE,C.DESCRIPTIONSPANISH,
C.FECHAINGRESO,S.FECHA AS FECHASALIDA,
C.CANTIDAD AS MATERIALENTRADA,S.CANTIDADMATERIAL AS MATERIALSALIDA,
C.CANTIDAD-S.CANTIDADMATERIAL AS FISICOALMACEN 
FROM CATALOGO C , SALIDAMATERIAL S WHERE C.ID_PARTENTRADA = S.ID_PARTNTRADA;
END 
GO

exec SP_SELECTINVENTARIO

CREATE PROCEDURE BUSCA_INVENTARIO
@NOMBREPROYECTO VARCHAR(100),
@NUMPARTE VARCHAR(100)
AS BEGIN
EXEC SP_SELECTINVENTARIO;

SET NOCOUNT ON; 
SELECT * FROM SP_SELECTINVENTARIO WHERE NAMECLIENTE LIKE '%' + @NOMBREPROYECTO 
+ '%' OR NUMPARTE LIKE '%' + @NUMPARTE + '%'
END 
GO

result of the first stored procedure

sql sql-server stored-procedure

asked by Daniel 17.11.2017 в 19:15

source

1 answer

Problem with promise when collecting values that returns Replace data in a MySQL column

score 3 · Accepted Answer

Even though an SP has the ability to execute a query and return results, it is not possible to use an SP in clause FROM of a query, as if it were a table:

SELECT * FROM SP_SELECTINVENTARIO ...

If your SP accepts parameters, I would recommend converting it to a table-valued function . But since your SP does not even need parameters and is simply a way to encapsulate a query, it is best to define it as a view :

create view VW_SELECTINVENTARIO AS
SELECT C.NAMECLIENTE AS NOMBREPROYECTO,C.NUMPARTE,C.DESCRIPTIONSPANISH,
C.FECHAINGRESO,S.FECHA AS FECHASALIDA,
C.CANTIDAD AS MATERIALENTRADA,S.CANTIDADMATERIAL AS MATERIALSALIDA,
C.CANTIDAD-S.CANTIDADMATERIAL AS FISICOALMACEN 
FROM CATALOGO C , SALIDAMATERIAL S WHERE C.ID_PARTENTRADA = S.ID_PARTNTRADA;
go

There you can use it inside your other SP by adding additional filters:

CREATE PROCEDURE BUSCA_INVENTARIO
@NOMBREPROYECTO VARCHAR(100),
@NUMPARTE VARCHAR(100)
AS BEGIN

SET NOCOUNT ON; 
SELECT * FROM VW_SELECTINVENTARIO WHERE NOMBREPROYECTO LIKE '%' + @NOMBREPROYECTO 
+ '%' OR NUMPARTE LIKE '%' + @NUMPARTE + '%'
END 
GO