Partial sprocs?

Dave and I have written a T4 template to generate Entity Framework-friendly INSERT, UPDATE, and DELETE stored procedures.  I’ll detail the template in a future post.  I had the crazy idea of implementing something similar to C#’s partial methods in SQL, which I named “partial stored procedures.”  The reason behind the idea is the same reason partial methods exist: a code generator (a T4 template in our case) generates the same code every time, overwriting any changes someone might have made to the generated code.  The Entity Framework and LINQ to SQL designers create partial methods in the generated code to provide points where a developer can execute code, such as before and after a property changes.  It’s possible to do something similar when generating stored procedures, but the performance hit might be discouraging.  Here’s an example of what the code generator might create:

CREATE PROCEDURE [dbo].[Person_Insert]
    @name varchar(50)
AS
BEGIN
    IF OBJECT_ID(‘[dbo].[usp_BeforePerson_Insert]’) IS NOT NULL
        EXEC [dbo].[usp_BeforePerson_Insert] @name

    INSERT INTO [Test] VALUES (@name)
    SELECT SCOPE_IDENTITY()

    IF OBJECT_ID(‘[dbo].[usp_AfterPerson_Insert]’) IS NOT NULL
        EXEC [dbo].[usp_AfterPerson_Insert] @name
END

I did a small amount of testing while I played around with the idea.  Inserting 100,000 rows into the two-column table using a regular stored procedure took 31 seconds.  After adding the additional SQL, but without creating the “partial” stored procedures, 100,000 executions took 35 seconds to complete.  When I created the “before” stored procedure with a simple SELECT in it, the time rose to 54 seconds.  Although it’s an interesting idea, there isn’t really any need for something like partial methods in SQL.  The same thing can be accomplished by adding INSTEAD OF and AFTER triggers to the table.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s