0

I am in the process of creating a purge script in VBScript, which deletes records from tables if they are more than one month old.

The criteria for each table is different, and there are both parent and child tables (i.e. the latter is dependent on the former for retrieving ID codes and upload statuses).

My question: Should each DELETE block for each table be created as a separate procedure, including child and parent deletions? How would this best and most efficiently be handled?

A sample of the SQL code:

DELETE FROM TestStaging.dbo.SpinDataEntries
WHERE TestStaging.dbo.SpinDataEntries.SpinDataId IN
(select TestStaging.dbo.SpinData.SpinDataId from TestStaging.dbo.SpinData
WHERE TestStaging.dbo.SpinData.UploadStatus in ('F', 'C')
AND LastUploaded < DATEADD (m,- 1,SYSDATETIME()));

DELETE FROM TestStaging.dbo.SpinData
WHERE TestStaging.dbo.SpinData.UploadStatus in ('F', 'C')
AND LastUploaded < DATEADD (m,- 1,SYSDATETIME());

Should each of these two blocks, as well as the remaining code, be logged as separate stored procedures, and then called from a separate, main VBScript?

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328

1 Answers1

0

Unless you have a requirement to be able to run each statement separately it's more efficient to wrap them in a single stored procedure.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
  • Thanks, so the individual SQL blocks would have their own `BEGIN TRAN` and `END TRAN` tags, right? –  Oct 06 '14 at 20:50
  • @vordhosbn Normally you'd want either all statements to complete, or none (that's what transaction ensure). If you want the individual statements to complete even if another statement fails you could enable auto-commit (`SET IMPLICIT_TRANSACTIONS OFF`) instead of wrapping each statement in a separate transaction. – Ansgar Wiechers Oct 07 '14 at 06:45