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]
IF OBJECT_ID(‘[dbo].[usp_BeforePerson_Insert]’) IS NOT NULL
EXEC [dbo].[usp_BeforePerson_Insert] @name
INSERT INTO [Test] VALUES (@name)
IF OBJECT_ID(‘[dbo].[usp_AfterPerson_Insert]’) IS NOT NULL
EXEC [dbo].[usp_AfterPerson_Insert] @name
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.