Stored procedures in SQL Server master-child relationships not tables

Question

In college last year I came across something I found very interesting with regard to stored procedures. It was mostly with regard to reducing code repetition for transaction management, error handling, and security. But I have researched it since and cannot find it anywhere. Perhaps I don't know what it is called so I will explain.

Let's say I have a simple table (forget its relationships, just a single table). I have at least 5 possible operations I can do on it namely CRUD, with the R being read the details of one row, or read a list of rows for a given criteria. Again, let's not get into too much detail with complicated stored procedures and let's pretend, for this example, we want nothing but the following 5 operations to be performed:

• Create (simple insert)
• Read (one for reading a single row)
• Read multiple (reading a list of rows, simple select * from sometable where some condition, nothing complicated),
• Update (simple update)
• Delete (again, simple delete).

For the purposes of this example, a simple table with 2 or 3 columns, and the most simple procedures you can come up with to do those operations.

The problem:

My lecturer, when talking to us about transactions, advised us to take these simple procedures, and keep them separate as is normal. But then to write a master procedure which executes each of these. This means we have a master procedure into which we pass a character lets say 'C' for create and so on, and a series of conditions which decide which job to run. Of course, we should pass in the details needed from the master to the child proc which means we need to get them from the user as parameters. This master procedure is already beginning to sound complicated.

His reasoning, do the transaction handling, validation, error handling, and security handling in the master procedure. Then call that master procedure with the required parameters, which does all the checking, and passes the parameters to the child procedure. Below is an example.

Simple table, don't go worrying too much about relationships, keys, indexes, constraints, triggers and so on.

For the below two pieces of code, the only real relevant bit is the parts between the try...catch. The rest is boiler plate for the master.

Below is the code for a create procedure:

ALTER proc [Actions].[AreaCreate]
--External Variables - Input
@AreaName varchar(50),  @AreaAvailablity bit,   @Description varchar(max),

--External Variables - Output
@NoOfRecords int output

as
--Internal Variables
declare @ErrorMessage varchar(max)

Begin try
    insert  [Tennis3rdYrMVC].[dbo].Areas
            ([AreaName],    [AreaAvailablity],  [Description])
    values  (@AreaName,     @AreaAvailablity,   @Description)

    --Show # of records affected so you can detect nulls or empty lists
    --and handle them as you choose in the code
    set     @NoOfRecords = @@ROWCOUNT
End try
Begin Catch
    set @ErrorMessage = ERROR_MESSAGE()
    raiserror(@ErrorMessage,16,1)
    return ERROR_NUMBER()
End Catch

--All Ok
return 0

Below is another piece of code for a delete procedure

ALTER proc [Actions].[AreaDelete]
--External Variables - Input
@Id int,

--External Variables - Output
@NoOfRecords int output

as
--Internal Variables
declare @ErrorMessage varchar(max)

begin try
    delete from [Tennis3rdYrMVC].[dbo].Areas
    where Id = @Id

    --Show # of records affected so you can detect nulls or empty lists
    --and handle them as you choose in the code
    set @NoOfRecords=@@ROWCOUNT
end try
begin catch
    set @ErrorMessage = ERROR_MESSAGE()
    raiserror(@ErrorMessage,16,1)
    return ERROR_NUMBER()
end catch

--All Ok
return 0

Finally, the suggested, and complicated master proc.

ALTER proc [Admin].[AreaMaster]
--External Variables - Input
@JobType int, -- 1=Create, 2=Read 1, 3=Read Many, 4=Update, 5=Delete
--Master sprock uses null defaults because not every job requires every field to be present.
--i.e. We dont need to know the area name of an area we want to delete.
@Id int                 = null,     @AreaName varchar(50)       = null,
@AreaAvailablity bit    = null,     @Description varchar(max)   = null,

--External Variables - Output
@NoOfRecords int = null output --Used to count the number of records affected where needed.
as
BEGIN
    --Internal Variables
    declare @ErrorMessage varchar(max)
    declare @return_value int

    -- SET NOCOUNT ON added to reduce network traffic and speed things up a little.
    SET NOCOUNT ON;

    /*
    --VALIDATION

    --Logic for ensuring all required values are entered should be done in processing below in
    --the master sprock, NOT in the children (i.e. why check for valid id in 5 sprocks when you
    --can do it here in one).


    We will do all the processing needed to ensure valid and required values are entered where
    needed.

    --SECURITY
    This is also where we put the security code required to stop SQL Injection and other
    attacks, NOT in the child sprocks. The child sprocks would not be allowed to execute by
    pages directly.

    */

    --Once all validation is done, call  relevant child sprocks

    --Call AreaCreate Sprock
    if(@JobType='1')
    begin
        exec    @return_value = [Actions].[AreaCreate]
                @AreaName           = @AreaName,
                @AreaAvailablity    = @AreaAvailablity,
                @Description        = @Description,
                @NoOfRecords        = @NoOfRecords OUTPUT

        --select    @return_value 'Return Value'

        if @return_value<>0
        begin
            raiserror('Error: Problem creating area.',16,0)
            --rollback transaction
            return 99
        end
    end


    --Call AreaShowDetail Sprock
    if(@JobType='2')
    begin
        exec    @return_value   = [Actions].[AreaShowDetail]
                @Id             = @Id,
                @NoOfRecords    = @NoOfRecords output
        ----Testing
        --select    'Return Value' = @return_value

        if @return_value<>0
        begin
            raiserror('Error: Problem reading area details.',16,0)
            --rollback transaction
            return 99
        end
    end


    --Call AreaShowList Sprock
    if(@JobType='3')
    begin
        exec    @return_value   = [Actions].[AreasShowList]
                @NoOfRecords    = @NoOfRecords output
        ----Testing
        --select    'Return Value' = @return_value

        if @return_value<>0
        begin
            raiserror('Error: Problem reading areas list.',16,0)
            --rollback transaction
            return 99
        end
    end


    --Call AreaUpdate Sprock
    if(@JobType='4')
    begin
        EXEC    @return_value = [Actions].[AreaUpdate]
                @Id                 = @Id,
                @AreaName           = @AreaName,
                @AreaAvailablity    = @AreaAvailablity,
                @Description        = @Description,
                @NoOfRecords        = @NoOfRecords OUTPUT

        --select    'Return Value'      = @return_value

        if @return_value<>0
        begin
            raiserror('Error: Problem updating area.',16,0)
            --rollback transaction
            return 99
        end
    end


    --Call AreaDelete Sprock
    if(@JobType='5')
    begin
        exec    @return_value   = [Actions].[AreaDelete]
                @Id             = @Id,
                @NoOfRecords    = @NoOfRecords output

        --select    'Return Value'  = @return_value
        if @return_value<>0
        begin
            raiserror('Error: Problem deleting area(s).',16,0)
            --rollback transaction
            return 99
        end
    end

    --All Ok
    return 0
END

Is that complicated or what? Now imagine another level which if you have several tables to operate on, will decide which sub master to call, and this MasterMaster for want of a better name must have external parameters for every field in every table. However, the validation, security, and transaction code would be moved up to that level instead.

More importantly, is that the way a professional would do it. And finally, I am using MVC with entity framework. Suppose I have a database like this (I have), and access is only allowed through this master stored procedure (it is). How do I call such a stored procedure from within EF and MVC. Even better, how would I bypass EF all together and get the data into my controller in a way my view will understand. I know how to do this with ASP.Net code behind. But not in MVC.

Any suggestions. Please, feel free to tell me that this is completely crazy as it seems like a hell of a lot of work (what if you have 50 tables (I do). Imagine, 5 simple operations, across 50 tables = 250 child stored procedures + 50 Sub-Sub-Masters, + 5 Sub-Masters + 1 Master. I have my work cut out. Is there a tool that can do this or at least generate a template for me?

So to summarise, My questions are:

1. Is this overly complicated or is this the professional way to do this?
2. What is the term used to describe this Master / child procedural relationship.
3. Is there a tool that can automate this process.
4. Is there a tool that can help me generate some kind of boilerplate template.
5. How can I use this procedure with EF and MVC
6. How can I bypass EF altogether and just find a way to call the procedure directly.
7. Is there anything like the equivalent of classes, or even methods in T-SQL which can structure this much like a standard piece of code. Meaning that each child procedure is a method, each table has its properties, and the constructor will be the the initial (or altered) representation of the object.
8. If 7 does exist, which would be more standard, the way I described (i.e.) splitting everything up, or the idea in 7.

With regard to this point 4 I am talking about something simple, like the one already in SQL Server where you right click a procedure and choose execute and it generates some simple code for testing your procedure, or like the one for making functions.

Thanks for reading this and for any help you can offer.

Answer 1

In theory, your lecturer is presenting a good concept. The idea of thinking of certain types as top-level domain models makes a good deal of sense. Objects which understand how to save their immediate children can be very useful as well.

Beyond the theory, I disagree with the implementation, mainly because of its (needless) verbosity and inflexibility.

Stored Procedures

Stored procedures are great, but one of the benefits of using an ORM (like EF) or any automation framework is that you don't have to write boilerplate code over and over which does the same thing. Over the years, I've greatly reduced the number of CRUD-only procedures in my applications, preferring instead to auto-generate the statements as needed (again, this is a function of a good ORM).

Read
Most applications require looking at the same data in different ways, and it is often advantageous to have an unlimited number of options for querying that data. Maybe you want the whole record; maybe you want a single field. Maybe you want one level of the object hierarchy or perhaps you want to flatten the object hierarchy into a view model.

Read Many
When I do write stored procedures, I tend to put my effort into highly-optimized stored procedures for efficiently browsing/searching data.

In most business applications, read-many ends up being a summary view of data. It's trivial to return 1000 narrow/flat records as a result of a search; but retrieving the complete hierarchy for 1000 complex objects would be extremely inefficient and usually is not needed.

In fairness, your master proc could return an appropriate summary view, but I refer to my earlier point of being able to look at the data in multiple ways. Stating a single read-many behavior is an unneeded limitation.

Delete
Deletes are sometimes good candidates for stored procedures. There is no point in loading 100 records into the business tier just to get their IDs and issue a DELETE one by one.

Transactions
In my opinion, the most flexible transaction support often comes from the business tier. See my answer to "Transactions should be handled in .NET or SQL Server?": https://stackoverflow.com/a/12470061/453277

Top-Level Models

"Person" is a good candidate for a top-level model, while "Address" may be a child of that model.

The idea of one master procedure facilitating all access is completely impractical. Though you may only present People-management screens to the user, under the hood you may wish to access an Address object without the need or knowledge of the Person to which it is related.

A simple example is a Person profile page with a list of addresses; you click on an "Edit" button to launch a modal window to edit the details of the Address. Do you really want to have to go through the Person-management logic just to get the Address data? You might perform a security check related to the parent; you might create a new relationship to the parent; you might do nothing at all. Flexibility and reusability are key here.

Furthermore, when you decide to add a Company->Address relationship in addition to the Person->Address relationship, you don't want to have to recreate any of this logic.

Parent/Child Relationships

There is not always a definable parent/child relationship between two related objects. My favorite example is a Person and a Document.

• A person may have a list of documents they authored.
• A document may have a list of authors.

Which one is the parent?

To the database, it rarely matters. There is probably a PersonDocument table somewhere with two integer columns; it doesn't matter which is the parent. Again, in the business tier flexibility is key. You should be able to access Document with a list of people as "children", and a Person with a list of documents as "children".

I like to think of all relationships as potentially bidirectional. It never hurts to think of them as bidirectional, but forcing a parent/child hierarchy can be limiting.

Security

SQL Injection
I noted this in the comments of your master proc:

This is also where we put the security code required to stop SQL Injection and other attacks, NOT in the child sprocks. The child sprocks would not be allowed to execute by pages directly.

Calls to stored procedures should always be properly parameterized, which provides the needed protection against injection. The mention of SQL injection here is either irrelevant or alarming, as it suggests the calls have not been properly prepared.

Securables
A discussion of object-level security in SQL Server is far outside the scope of this post, but I will mention that you can achieve extremely granular security without using a master procedure.

Recommendations

• If you are using an ORM (like EF), let it do its job and write this plumbing code for you. Don't try to force a different paradigm onto it. If you must use your lecturer's method (e.g. for an assignment) it may be easier to remove EF from the equation.

• Flexibility is key (third time I've said it). No matter how great a paradigm you develop, you will need to extend it or deviate from it at some point. Being able to do that is critical.

• Even if two types are seemingly unrelated, you should be able to manipulate them within the same transactional and logical context.

• As @Habibillah pointed out, don't forget about testing/testability.

• Don't reinvent the wheel when you need to deliver on a project (i.e. use existing tools which get the job done). However, this is a good question and creating your own object persistence/retrieval methodology is an excellent academic exercise.