Are there any advantages in using a single stored procedure for multiple operations or is my teacher-CodePudding

I'm working on my databases class final project, which consists of making an application that can access and do operations in a database. My teacher insists that I should use a single stored procedure per table:

--In stored procedure "sp_Ciudades"
    @Opcion int,
    @IdCiudad int = NULL,
    @IdEstado int = NULL,
    @Nombre varchar(50) = NULL
AS
BEGIN
    --Create new record
    IF @Opcion = 1
        BEGIN
            INSERT INTO Ciudades
            VALUES(@IdEstado, @Nombre)
        END
    --Delete record
    IF @Opcion = 2
        BEGIN
            DELETE FROM Ciudades
            WHERE IdCiudad = @IdCiudad
        END
    --Update city
    IF @Opcion = 3
        BEGIN
            UPDATE Ciudades
            SET IdEstado = @IdEstado
                Nombre = @Nombre
            WHERE IdCiudad = @IdCiudad
        END
    --Retrieve cities
    IF @Opcion = 4
        BEGIN
            SELECT *
            FROM Ciudades
            WHERE IdCiudad = @IdCiudad OR @IdCiudad = 0 OR @Ciudad IS NULL
        END
    --Fill cities ComboBox
    IF @Opcion = 5
        BEGIN
            SELECT IdCiudad, Nombre
            FROM Ciudades
            ORDER BY Nombre
        END
END

On the other hand I tried to do something like this:

--In stored procedure "sp_Ciudades_Vista"
    @IdCiudad int = NULL
AS
BEGIN
    SELECT *
    FROM Ciudades
    WHERE IdCiudad = @IdCiudad OR @IdCiudad = 0 OR @IdCiudad IS NULL
    ORDER BY ID
END

--In stored procedure "sp_Ciudades_Cambio"
    @IdCiudad int,
    @IdEstado int,
    @Nombre varchar(50)
AS
BEGIN
    UPDATE Ciudades
    SET IdEstado = @IdEstado,
        Nombre = @Nombre
    WHERE IdCiudad = @IdCiudad
END

I feel the code is much more organized (and encapsulated) and easier to read and understand when defining the command strings in my application (with the numeric options I constantly have to go back to the procedure definition in order to see what each number does). Also, I only use the parameters that are needed to achieve the operation.

However, my teacher says that if I do it this way, the database will have way too many stored procedures since in a real database, there are over 200 options per procedure, and the database may even crash if there's too many procedures in it.

I have already read a similar question and the most voted answer states that:

[...] a separate stored procedure for each operation is best. Otherwise you get too much logic inside your procedures.

But my teacher still insists I should be doing it the other way. Is my teacher's argument true? What are the advantages of having multiple options/operations inside a single stored procedure?

CodePudding user response：

Are there any advantages? Probably - opening Management Studio could be quicker.

Is your teacher wrong? Yes, most certainly, but as SMor and Jeroen write in the comments: Your teacher does the grading, so he's right until you graduate your class.

Take a look at this question: Generic Stored Procedure for ALL the tables

If you could write a single stored procedure for each table with Create, Read, Update and Delete, then why not build a single generic stored procedure with CRUD operations for ALL the tables? One database - One stored procedure.

Now, apply the logic you would use to determine that this is a bad idea to your problem.

Or apply SOLID principles - they apply to stored procedures as well. You wouldn't write a single method to do 4 wildly different operations based on an input parameter, would you?

Or have a look here: If logic in stored proc.

On first execution SQL Server will explore all the branches, and build a plan - but it will use the parameters seen at that first execution. So assume your first execution is an insert with @IdCiudad = null, then that's what SQL Server will optimize for in the option 4 branch.

Your initial insert will build a select plan (for option 4) where all rows are expected to be returned, meaning a too large memory grant and maybe a parallel plan with hash joins to boot.

Oh, and

WHERE Foo = @foo OR @foo = 0 OR @foo IS NULL

SQL Server won't know how to optimize for this. See Aaron Bertrands article here Kitchen sink design pattern.

CodePudding user response：

This approach violates the Single Responsibility Principle(SRP from SOLID). That not cool(