Using Stored Procedures to Update data

[glennfishwick]

I am in the process of investigating the approach of using stored procedures to hold all our data access and business logic.

I can see how you would use stored procedures to control database inserts. I can't see what the best way to control database Updates via stored procedures is.

i.e. If your only updating one column in a table from the client app do you pass just one column and a refno and expect the stored procedure to build dynamic SQL? Would this not be slower.

or

do you always pass all the columns and get the stored procedure to always update all the columns - the client may not always have all the column values!

or

Use an ADO recordset to control updates?

 

[williamu]

Well for what it's worth:

Using SP has distinct advantages over using std SQL queries. Firstly, it allows you to keep all your data maintenance code in the one location thus reducing the possibility of errors accross variations pages forms etc.

You also reduce network traffic and since SP are recompiled you save that overhead as well.

Normally you would know in advance what data was being sent to the client so in return you could write an SP to do the update of the data using the columns provided.

Granted you can end up with a lot of SP but IMHO SP are the way to go. Heck I even use them for SELECTs.

HTH

William
Software Engineer

 

[williamu]

Ooops


recompiled should say precompiled!

Sorry. Fatfingeritis I guess.
William
Software Engineer

 

[sagn]

I use SP's for almost everything.

Usually I write them with
flexibility in mind,. That is I have them
do 'dynamic' sql.

I would pass the columns I want to update
and create a sql statement based on that,

 

[bperry]

Hi glennfishwick:

Just adding further to williamu's comments, let's say you are doing a form in ASP or VB that displays several columns from a table to the user, and lets her optionally update the values. As williamu mentioned, you know these columns have been sent to the client, so when you write your code (again, ASP or VB) to call the updating SP, you pass back those same column values to the SP. Then code the the SP to update those particular columns: some of them will be new values, and some of them will be updating with the same old values (i.e. the user didn't change them).

But it won't be *all* the columns in the table....just the ones that you originally displayed to the user and you know are eligible to be changed.

I think that's how most people do it (although I'm not sure I've described it very well.)

hope this helped a little bit,
brian perry

 

[cmmrfrds]

I have nothing against stored procedures since I use then all the time, but

ADO is not a bad way to go under some circumstances. With Access projects (adp) an ADO connection is made to the SQL Server database and you can treat tables, queries, and Resultset SP's just like you would in an Access mdb. This will reduce development time and with an app that doesn't have too many concurrent users it can be relatively efficient, even then there are trade offs. Always tradoffs.

The ADO recordset that is returned to a form can have the updates batched back to sql server to reduce network traffic and overhead. This pushes the processing back to the clients which typically have more resources than they are using. This requires a little bit of coding since the connection needs to be closed after the recordset is returned and then reconnected prior to the batch update - this is minmal code, less than creating your own updates. If you are doing updates in a SP there is typically a call in the code to sql server for each update. I am not so sure the network traffic is reduced by using SP's. How is the network traffic measured?

 

[crystalized]

Hi,

I think when doing updates you have a couple of options in your stored procedures.

Step 1. First off (applies to both methods) you need to identify the crucial fields for the update. Generally I would consider these to be the ones you will use to identify the record to update although you may have other ones that will always be known regardless of what is calling the sp.

The first method would be to use a stored procedure with parameters for all the fields and default values of null for all the ones not identified in Step 1. Then inside the code of the stored procedure you alter the update statement according to which of the parameters are null (generally not including them in the update statement). I also just thought of this variation on this approach that you could do multiple update statements based on the not null parameters (although I can not imagine using this myself I see no reason why it would not work)

The second method would be to create a number of separate stored procedures that accept different sets of parameters. Keeping in mind that all the stored procedures will have the parameters defined in Step 1 at the very least.

Well at least this is the way I have approached the problem. I personally try to encapsulate as much of the business logic as is reasonably possible into the stored procedures I write. It is so much easier than having to code it in your application IMHO LOL

Hope this helps,

Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde