Sql server heritage in Procedure

Question

I’m having performance problems using views mapped in the EPH to use linq for grouping and paging. It would have a way to assemble a procedure basis, where I explained what it would take for all the procedures daughters implement?

Or some form of template?

Answer 1

Hi, Gilmar. All right?

At first you could check if creating indexes can improve the performance of your views, if you haven’t seen it yet. Another way to solve it is to materialize the data of the views in physical tables, but it can end up occupying enough space.

Regarding your last question, it is possible to assemble this structure yes. You can create a precedent (base) that has a output, and, from this output feed the parameter of the other variables. See the following example:

-- Declare the variable to receive the output value of the procedure.  
DECLARE @SalesYTDBySalesPerson money;  
-- Execute the procedure specifying a last name for the input parameter  
-- and saving the output value in the variable @SalesYTDBySalesPerson 

EXECUTE Sales.spGetEmployeeSalesExemplo_Base  
    N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;  

--Executando a procedure (Filha) com o parametro retornado da procedure base 
EXECUTE Sales.spSalesExemplo_Filha @SalesPerson = @SalesYTDBySalesPerson;  
GO

For more details you can check the documentation from where I adapted the code: https://docs.microsoft.com/pt-br/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-ver15

Remembering that, this is just one of the ways to solve the problem. A second alternative would be the "food" /popular base design of a table with parameters that will be used/consumed by the daughter procedures.

If you need help to assemble the solution, you can contact me. I’ll be happy to help.

Abs. Welricsson Carmo