2
\$\begingroup\$

While executing a SQL script in production like sensitive database, usually I do the following

BEGIN TRANSACTION

--Declare of test variables
DECLARE @ExpectedAffectedRows INT = 10 -- Manually count this
DECLARE @ActualAffectedRows INT = 0



-- Its a demo code, don't take it seriously ;P
DELETE * FROM SomeTable AS st WHERE st.ColumnA = 'A'
SET @ActualAffectedRows = @ActualAffectedRows + @@ROWCOUNT

UPDATE SomeTable
SET ColumnA = 'A' WHERE ColumnA='B'
SET @ActualAffectedRows = @ActualAffectedRows + @@ROWCOUNT

IF @@ERROR<>0 OR @ActualAffectedRows<>@ExpectedAffectedRows 
BEGIN 
    ROLLBACK 
    SET NOEXEC ON 
END

IF @@TRANCOUNT>0  
BEGIN 
    COMMIT 
END

The thing here, I hard coded the @ExpectedAffectedRows variable manually, counting how many rows will be affected.

Is the process fine or are there other superior processes or best practices to deal with this type of data update/delete things?

\$\endgroup\$
  • 1
    \$\begingroup\$ Are you trying to get away from manually counting the records? \$\endgroup\$ – scsimon Apr 24 '17 at 11:17
  • \$\begingroup\$ I would prefer to run some checks afterwards to make sure no sensitive data is left. Deficits and surpluses in actually affected rows may compensate and still produce the expected number (although this would be sheer coincidence). \$\endgroup\$ – Gert Arnold May 3 '17 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.