StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » CRUD via Stored Procedures or not?

CRUD via Stored Procedures or not?

Charles Thomas Blankenship — Wed, 29 May 2013 09:27:54 GMT

I've been using StrataFrame for over five years now and I've never pondered this question too hard ... until now.

You have two options when choosing the methods to perform an Update, Insert and Delete ... either provide a CRUD stored procedure (automatically generated by the DDT) or let the framework handle it.

I've currently made the choice to used Stored Procedures ... but I am beginning to doubt my decision for the following reasons: 1) it loads up the stored procedure list with the number of tables times three 2) I have to set six additional properties on each of the BOs.

As everyone knows, the more code there is and the more properties modified the more opportunities there are to introduce bugs into the system.

So, the big question is this: Why would anyone choose to go the stored procedure route?

PS: If one looks at the Table Properties for one of the RBS tables like SFSRoles, the M4 guys use the stored procedure option. So, what is their reasoning for it?

Thanks,

RE: CRUD via Stored Procedures or not?

Edhy Rijo — Wed, 29 May 2013 09:27:54 GMT

Hi Ben,

[quote][b]Ben Chase (5/20/2013)[/b][hr]It's really all semantics. We went the route of stored procedures initially, but we have since switched over to just letting the BOs generate the dynamic SQL. In SFv2, the dynamic SQL is much better than v1. For instance, updates only include the fields that have changed rather than blindly updating all fields.[/quote]
That is an excellent enhancement since I have been bitten before in a multi-user application where some flag fields are updated from different forms and then noticed some of my data changed without knowing the reason, so I had to create stored procedures to handle these flag fields updates.

RE: CRUD via Stored Procedures or not?

StrataFrame Team — Mon, 20 May 2013 07:50:49 GMT

It's really all semantics. We went the route of stored procedures initially, but we have since switched over to just letting the BOs generate the dynamic SQL. In SFv2, the dynamic SQL is much better than v1. For instance, updates only include the fields that have changed rather than blindly updating all fields.

The real reasons you would want to use stored procedures are:

1. Using an indexed view instead of a table for the business object; you would need to use an "instead of" trigger or a sproc to update the multiple tables

2. A DBA is running the database and wants to control CRUD access to individual users based upon permissions on the stored procedures.

SF uses parameterized queries, so the execution plan is cached off just like a sproc is, so the performance isn't really different between the two.

Some people just like using sprocs or have a DBA that requires the use of sprocs, so we give you the option of using them.