UPDATE AND DELETE PROBLEM INSIDE STOREDPROC

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

Transact-SQL (2000)

UPDATE AND DELETE PROBLEM INSIDE STOREDPROC

Author

Topic

Shafeeq
Starting Member

3 Posts

Posted - 2003-08-03 : 22:58:15

My objective is to have my storedprocedure update a table and delete the unupdated records.
-----------------------------------------------------------
CREATE PROCEDURE [dbo].[MyProc]
@x int
AS
DECLARE @DT datetime
SET @DT = GetDate()

UPDATE MyTable
SET Qty = Qty+@x, TransDate = @DT
WHERE Qty+x <> 0

DELETE FROM MyTable WHERE TransDate <> @Dt
-----------------------------------------------------------

But when I execute the storedproc I find that it is deleting all records including the updated ones, which means that deletion is taking place before the update got commited. I am sure I am missing out something, I am still trying to figure out how this could be done but no luck.

I would appreciate some ideas.

Thanks in advance
Shafeeq M.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-08-04 : 00:19:51

I just recreated that table and proc and it works fine for me.

Do you have any other things going on at the same time that affect those tables ? Maybe you could try putting a transaction around them and play with some locking options.

What happens when you just run those statements 1 at a time outside a proc ? Does it work then ?

Damian

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-08-04 : 01:25:58

Works for me too...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Shafeeq
Starting Member

3 Posts

Posted - 2003-08-04 : 06:04:53

Thank u all for the response.

I figured what was wrong after I received a response from MS Forum.

>The most likely thing I can think of is that you are comparing >against GetDate() ( or CURRENT_TIMESTAMP ),
>rather than a constant @Dt in your real stored procedure, and these >will change over the course of the
>execution.
>Andrew John.

So I found that the declared variable @DT was the culprit, it supplies the value of GETDTAE() everytime it is accessed rather than the original value it was initialized with.

Thanks again for the time spending
Rgds,
Shafeeq M.

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-04 : 07:16:44

quote:
So I found that the declared variable @DT was the culprit, it supplies the value of GETDTAE() everytime it is accessed rather than the original value it was initialized with.

I've been following this a little, and am pretty sure you conclusion is incorrect. The variable @DT will only return it's initial value, and will not recalculate GETDATE() every time it is referenced.

I'm willing to bet a beer on this. Any takers?

I don't know why your query wasn't working, but if you post some sample data from MyTable, it might help. (You could also post the intermediate data before the delete occurs. That would be interesting too.)

Sam

dsdeming

479 Posts

Posted - 2003-08-04 : 08:06:54

Sam's right. Once the @DT variable is initialized with GETDATE(), its value won't change unless you explicitly change it.

Take a look at the WHERE clause in your update statement. "Qty+x" may be the problem. If it prevents the update from happening, then all rows would be deleted.

Dennis

Shafeeq
Starting Member

3 Posts

Posted - 2003-08-05 : 02:49:20

I am sorry I was wrong about my conclusion, I affirm this after an acid test with T-SQL, which turned out the way, you all said it was.

Sorry abt Qty+x it was a typo, BTW SQL Server is smart enough to validate the code before saving and thus such errors wouldnot be permitted in the first place.

Thnx again for correcting me.
Regards,
Shafeeq M.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources