If I have the following SQL block (in SQL SERVER 2008 R2):
BEGIN
BEGIN TRAN
DELETE FROM dbo.fooData
WHERE LastUpdate < DateAdd(hour, -1,GETUTCDATE())
COMMIT
BEGIN TRAN
DELETE FROM dbo.barData
WHERE SessionID NOT IN (SELECT sub.SessionId FROM dbo.fooData sub)
COMMIT
RETURN 0
END
I assume that I should make an explicit COMMIT between statements so that the deleted data from fooData is displayed in the second delete. It's right? Ideally, I would like all this to be in one transaction. Example:
BEGIN
BEGIN TRAN
DELETE FROM dbo.fooData
WHERE LastUpdate < DateAdd(hour, -1,GETUTCDATE())
DELETE FROM dbo.barData
WHERE SessionID NOT IN (SELECT sub.SessionId FROM dbo.fooData sub)
COMMIT
RETURN 0
END
I am afraid that the second statement will not receive the first deleted data. Note that the return is because it is part of the stored procedure. I'm not interested in cascading deletions or attachments; I'm somewhat limited to this method.
Brian source
share