Are there any advantages in using a single stored procedure for multiple operations or is my teacher wrong? [closed]

Question

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?

Answer 1

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.