Advice 1

[eveCalypso]

Good Day,
I have used Stored procedures for all of my SQL requirements from code (new language plus new at stored procs for SQL. So please be patient!)

I would like to now build on this layer of knowledge by building in transaction processing on the DB side, or on the coding side if not possible.

I know you can start, commit & rollback within a stored procedure, but what do you do if the logical flow of work is as follows:

selection
calculations1
some updates to the DB
calculations2
some inserts to the DB
wrap up calculations3
a delete off the DB

Now, I wrote these DB Stored Procedures to be re-usable in other parts of the program if neccessary - also, I can not just do all of the above in one go.

However, I would like all of them to fail if one failed - as this is in essence one transaction...
Any advice on how I can achieve this?? I am coding in VB with a SQL Server 2000 back-end.

Thank you in advance
Regards,
EvE

 

[jgoodman00]

The simplest way is probably to create another 'wrapper' sp which calls each of the other sp's. The wrapper sp can have a single transaction in it.

However, if this sequence of transactions carry out a lot of modifications you are going to get into the loop of long-running transactions which are not good.

Is is possible to re-arrange the workflow:
Selection
Calculations1
Calculations2
Calculations3
Begin Tran
Updates
Inserts
Delete
Commit Tran


This will reduce the length of the transaction, & you wont be holding record locks for such a long time.

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[eveCalypso]

Mr Goodman,

Thank you for your reply. I had a look at my calculations. I iterate through the results of the initial selection in order to do the updates and/or neccessary inserts. That is the first problem.

The second problem is that there will only always an update, there might be a few inserts and always just one delete...

The "Wrapper" sounds good - apart from the fact that I can not have a delete in it.

Thank you for your advice in advance
Regards,
EvE

 

[jgoodman00]

Ok, I am not sure I follow. Why cant you have the delete inside the wrapper?

I just tried this. I created 4 sps:

CREATE PROCEDURE TEST1
AS
BEGIN TRAN T1
EXEC TEST2
EXEC TEST3
EXEC TEST4

IF @@ERROR = 0
	COMMIT TRAN T1
ELSE
	ROLLBACK TRAN

CREATE PROCEDURE TEST2
AS
INSERT INTO tblLUPSurveyor(Surveyor) VALUES ('JZ')

CREATE PROCEDURE TEST3
AS
UPDATE tblLUPSurveyor SET Surveyor = 'JA' WHERE Surveyor = 'JZ'

CREATE PROCEDURE TEST4
AS
DELETE FROM tblLUPSurveyor WHERE Surveyor = 'JA'

This works as I expected, although it does hold locks for the duration of the transaction...



James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[eveCalypso]

Mr Goodman,

Sorry, perhaps I wasn't clear enough.
With each record:
a) Depending on what the outcome of the calculations are
there will be AT LEAST one UPDATE
b) Again, depending on the outcome of the calculations,
there will be AT LEAST one INSERT, but not
neccessarily as many as INSERTS as there were updates.
c) There will always just be a SINGLE DELETE.

So, (a) might run x times, (b) too but perhaps x-y times and (c) once, depending on what needs doing.
The above stored proc assumes that there will be a delete for each Insert/Update call, I am correct?

Regards,
EvE

 

[jgoodman00]

I understand now. Assuming each individual sp handles all of its own task (e.g. update) it wont actually matter how many updates deletes etc occur within each sp.





James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[eveCalypso]

Mr Goodman,

Yes, except that I would like to handle each record's successful insert(s)/update(s) & delete as a single transaction.

Its a stock system, so the data will be incorrect/inconsistent if any of the steps PER record should fail...

Thank you again for your feedback.
Regards,
EvE

 

[eveCalypso]

Mr Goodman,
I just had a thought... I am very new to Stored Procs - can you have IF constructs and declare usable variables in them?
So can I conceivably call my SELECTION SP, iterate and do all the calculations within an sp rather than code...?

If possible, what would the advantages/disadvantages be?
Obviously, I do need some good error handling to return to my app if neccessary.

Regards,
EvE

 

[jgoodman00]

Yes, TSQL is very powerful & supports IF ELSE, CASE etc.

You will be much better off doing all data processing in an sp, as this is carried out server side, so you remove the need to copy the data to the client, modify it & copy it back to the server...

You might also want to look into using triggers to maintain integrity between your tables, which can be very useful in stock tracking db's. They are essentially sp's which automatically run when data is inserted/updated/deleted on a specific table.



James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[eveCalypso]

Ok, it is clear now!
I will endure a bit of a learning curve now for great benefit later - I can see the sense in that.
I will look up T-SQL internet references and get started, but don't be surprised if I shoot off a few more questions before this one is done!

Just a quick last question... I assume I can access the set of results from another SP and iterate through the records in the main SP?

Triggers sound like just the thing for one or two of my updates.

Again, thank you very much for your help and patience.
Regards,
EvE

 

[jgoodman00]

SQL BOL is a great resource, as is Inside SQL Server by Kalen Delaney. SQL BOL can be downloaded from the Microsoft site (there is a recent update to it as well).

Based on your description, I think you could end up using a cursor in the wrapper sp, & enumerating through the records in a table. For each record in this table you will then call several sps which will do different tasks. This being so, you shouldnt have a problem, just make sure you dont create any deadlocks (which can be very easy to do!).

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[eveCalypso]

Thank you Mr Goodman!!!
I am well and truly warned now

Regards,
EvE

 

[SQLSister]

I am somewhat worried about the fact that you are iterating through the records to do inserts or updates; this is an inefficent way to process and can almost always be avoided. (I can't tell you how without seeing what you are trying to do.) You need to write insert and update statements that have the calucations embedded in them. Then you do 1 insert, 1 update, 1 delete and you have the tables locked up in a transaction for much less time.

Why do I say this? I presume you are using a cursor. SOmething may take minutes to processs ina cursor if you have a lot of records, and miiliseconds if you use set-based statements. Clearly, if your system will have many records eventually, any method which iterates through records is a poor coding practice.

 

[eveCalypso]

Good Afternoon,
I tried to answer you explaining what I need to do, but it is too complex.
Firstly, as I explained in the previous posts:
I am NOT iterating through the records to insert/update.
I am iterating through the subset of transactions (which have not been assigned before) for a specific item in order to do calculations from each of these and

if REQUIRED only insert/update a different/same set of records. Only after iterating through all the records applicable, will there be a SINGLE delete.

So, to answer your concerns:
1) I will never have more than 5 or max 7 records to iterate through.
2) I have not yet decided how I will iterate through these records and are open to suggestions
3) I do not always know whether I will need an insert or update. It all depends on the outcome of the calculations.

My concern is about making this entire process one transaction.
I do hope that in the end I don't embrace poor coding practice - perhaps why I called my thread "advice"

Your comments will be appreciated.
Regards,
EvE