fabyola
asked on
Updating fields with StoredProcedures
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 ?
ASKER
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 ?
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
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
ASKER
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 ?
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So if it was you, what would you use if the application is Client/Server ?
If it was me and the application is Client/Server, I would use Stored Procedures.
ASKER
Especially when the client isn´t a good machine like a Pentium 100. Right ? Thank you for your help.
Yea, especially when the client i a slow machine like a Pentium 100. You are welcome fabyola.
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)
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