Solved

Updating fields with StoredProcedures

Posted on 2004-04-12
10
132 Views
Last Modified: 2010-04-05
I´m using Interbase 6.0. I have an application in which everytime a customer buys something I have to update two tables. One is the Item table that I have to subtract the number of items the customer bought. And the other one is the Clients table that I have to Sum the total of it´s purchase. I want to make a StoredProcedure to do it for me. Does anyone have any examples ? And if it´s better doing it with StoredProcedure or with Querys ?
0
Comment
Question by:fabyola
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 12

Expert Comment

by:esoftbg
ID: 10806290
This stored procedure is just from head, it is not tested:

ALTER PROCEDURE PROC_ITEM_INSERT
(
  CUSTOMER_ID INTEGER,
  ITEM_ID INTEGER,
  AMOUNT DOUBLE PRECISION,
  PRICE DOUBLE PRECISION
)
RETURNS
(
  RESULT INTEGER
)
AS
  DECLARE VARIABLE A DOUBLE PRECISION;
  DECLARE VARIABLE D DOUBLE PRECISION;
  DECLARE VARIABLE I INTEGER;
BEGIN
  RESULT = - 9;

    IF (AMOUNT IS NULL) THEN
      AMOUNT = 0;
    IF (PRICE IS NULL) THEN
      PRICE = 0;

    SELECT AMOUNT FROM ITEMS WHERE (ITEM_ID=:ITEM_ID) INTO :A;
    IF (A IS NULL) THEN
      A = 0;
    A = :A - :AMOUNT;
    UPDATE ITEMS SET AMOUNT = :A WHERE (ITEM_ID=:ITEM_ID);

    D = AMOUNT * PRICE * 100;
    I = CAST(D AS INTEGER);
    D = CAST(I AS DOUBLE PRECISION);
    D = :D / 100;
    SELECT MONEY FROM CUSTOMERS WHERE (CUSTOMER_ID=:CUSTOMER_ID) INTO :A;
    IF (A IS NULL) THEN
      A = 0;
    A = :A + :D;
    UPDATE CUSTOMERS SET MONEY = :A WHERE (CUSTOMER_ID=:CUSTOMER_ID);

    RESULT = 0;
  END
  SUSPEND;
END
0
 

Author Comment

by:fabyola
ID: 10806688
I want to pass the value of the pruchase or the amount of the item to the StoredProcedure and it will do the update for me. Is it better doing it with StoredProcedure the way I wan or with Querys ?
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 10806899
In your case it is better to use a stored procedure, because you will send parameters for updating once, then stored procedure will update 2 tables. If you use Query it will need to be executed 2 times with different SQL.Text (or 2 Queries executed each 1 time).
Another reason could be if needed some changes:
 - If you use a stored procedure you must alter the database (sometimes it is difficult);
 - If you use query you must to recompile the application with new features.
I think it is difficult to make decision about that.

emil
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:fabyola
ID: 10807024
What I want to do is have one StoredProcedure for each table. Do you think it will decrease traffic if I use the StoredProcedure to do the job ? Passing the value and the table name ?? Where am I going to get more ?
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 10807105
Stored procedures provide:
                Modular design: stored procedures can be shared by applications that access the same database, eliminating duplicate code, and reducing the size of applications.
      Streamlined maintenance: when a procedure is updated, the changes are automatically reflected in all applications that use it without the need to recompile and relink them. They are compiled and optimized only once for each client.
      Improved performance: especially for remote client access. Stored procedures are executed by the server, not the client, which reduces network traffic.
0
 
LVL 12

Accepted Solution

by:
esoftbg earned 125 total points
ID: 10807344
>What I want to do is have one StoredProcedure for each table.
It is possible to manage a table using only a StoredProcedure passing an additional parameter:

ALTER PROCEDURE PROC_ITEM_INSERT_UPDATE_DELETE
(
  PROCESS VARCHAR(3),
  ITEM_ID INTEGER,
  NAME VARCHAR(32),
  AMOUNT DOUBLE PRECISION,
  PRICE DOUBLE PRECISION
)
AS
BEGIN
  IF (PROCESS='INS') THEN
  BEGIN
    INSERT INTO ITEMS .......
  END;
  ELSE
  IF (PROCESS='UPD') THEN
     UPDATE ITEMS SET...... WHERE ITEM_ID=:ITEM_ID;
  BEGIN
  END;
  IF (PROCESS='DEL') THEN
  BEGIN
    DELETE FROM ITEMS WHERE ITEM_ID=:ITEM_ID;
  END;
  SUSPEND;
END
0
 

Author Comment

by:fabyola
ID: 10808002
So if it was you, what would you use if the application is Client/Server ?
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 10808212
If it was me and the application is Client/Server, I would use Stored Procedures.
0
 

Author Comment

by:fabyola
ID: 10812255
Especially when the client isn´t a good machine like a Pentium 100. Right ? Thank you for your help.
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 10812771
Yea, especially when the client i a slow machine like a Pentium 100. You are welcome fabyola.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Intraweb download file link ? 1 167
how to send memory stream from ics Client To ics server ? 11 175
Multiple image collision 13 88
Microsoft Access 97 and Delphi XE2 9 71
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question