Combining Stored Procedures, is this a good idea or bad idea?

[zachattack05]

So if I have 3 stored procedures that act on a single table (or multiple tables for that matter), is it acceptable to combine them since they are related on some level and then using a switch or if statement to determine which "procedure" to actually run?

As a simplistic example, assume 3 stored procedures, one for creating a record (CreateRecord), one for updating a record (UpdateRecord) and one for deleting a record (DeleteRecord). Can I simply combine them into one procedure called something like RecordManager, or RecordController or whatever, and just have an additional parameter that says which of the 3 to execute?

I know it's actually possible to do this, but I'm asking is it a good idea? Has anyone ever seen this and said "whoever did this was awesome" or "*sigh* Why me?"

I'm always worried about the future and not being able to remember what something is for and I'm not sure if this would help or hurt that worry?

Thoughts?

[skatamatic]

I, personally, would keep them seperate on the SQL side, but possibly have a RecordManager in the client side (C#) that would enumerate these operations. But it's totally up to you! If you are working with others just make sure you document everything, or at the very least make sure it makes a lot of sense why you are doing what you are doing.

[thines01]

That seems pretty logical.
If you set each up as a separate command (before the switch/case statements),
you should still receive the benefit you're expecting.

[zachattack05]

skatamatic,

I kinda already have that since I'm using data sets, but one thing that frustrates me is that I don't know how to put xml documentation on the dataset methods so I'm just worried that as the number grows, they will just mesh together and be frustrating and confusing to wade through. I think I might wrap it up in another class that I can "combine" related ones and actually document the methods.

Thanks for the idea! I like it!