Need help in writing code for a Transaction

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help in writing code for a Transaction

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-03-20 : 07:55:03

Hi

For my web application, I have product prices in a table.
For an operation, I need to drop the Price table, re-create it and re-populate it with some value. As you can imagine, I need to make sure that all the update are successful before I commit any changes to the Price table. I have never written a "Transaction" code, so I need your help to verify my following code:

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

CREATE PROCEDURE UpdatePrices

AS

BEGIN TRANSACTION Prices_Transaction

-- drop the table
drop table Prices

-- create the table again
Create table Prices(...)

-- insert the prices
Insert into Prices (...)

IF @@ERROR > 0
BEGIN
RAISERROR ('Update of Prices failed', 16, 1)
ROLLBACK TRANSACTION Prices_Transaction
RETURN 99
END

COMMIT TRANSACTION Prices_Transaction
GO
------------------------------------------

Is my above code good enough to carry out the transaction ??

regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-20 : 07:59:52

Instead of dropping the table, why dont you delete or truncate it?

Madhivanan

Failing to plan is Planning to fail

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-03-20 : 08:07:08

Well, you guys know best. So whatever advice you guys give me, I will follow that blindly.

The Prices table has 14000 rows & 25 columns

(1) So what is best: drop table OR delete / truncate?
(2) Is the transaction code fool proof?

regard

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-20 : 08:15:24

Whe you want to run updatePrices sp, do you want to add new records? Then why dont you have datetime column with getdate() as default value so that you can retreive them when needed. When drop or delete table, you lose the data. Give more details.

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-20 : 08:16:55

What is your requirement ? What are you trying to achieve ? Are you trying to refresh the records in the table with new sets of records ?

It doesn't sound like a good idea to drop and recreate the table.

Instead of deleting / truncate, can you do
- update whereas changes
- insert whereas new
- delete whereas not exists ?

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-03-20 : 09:21:19

http://sqlteam.com/forums/topic.asp?TOPIC_ID=63327

As I was discussing in the above post, I have 2 tables: WebPrices & AdminPrices

WebPrices is used for web application and AdminPrices table is used by the admin staff to update / insert / delete the prices.

Then I need to reflect the changes from AdminPrices to WebPrices table (WebPrices table would need to contain exactly the same data as AdminPrices table).

It seems to me the consensus is to use update/insert instead of drop table.

regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-20 : 09:28:12

Use Kristen's method to update the records and insert new records using

Insert into table1(columns)
select columns from table2 T2 where not exists(select * from table1 where keycol=T2.keycol)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources