If You Delete a Stored Procedure

By:   |   Updated: 2016-04-20   |   Comments (iv)   |   Related: More > TSQL

Problem

In my final tip, Best Practices for Deleting SQL Server Data, I showed some examples of how you lot tin can get into trouble when deleting data from the backend database. I also mentioned in the adjacent steps that there are some means you tin protect yourself when you lot practice accept to delete data this manner. This tip volition give you one method for ensuring you lot are deleting the number of records you are expecting to be deleted.

Solution

When it comes to best practices I am e'er in favor all DML changes beingness made through an application and not being done straight against the database using T-SQL commands. When washed this way nosotros tin ensure that the code has been tested to validate that it is going to do what it is intended to practise. That said, there are always cases that come up, whether information technology exist from a bug in an application calculation/updating records incorrectly or someone running a script against the database that was not fully tested or even providing the incorrect file to a data loader service/application when we are tasked with removing this unwanted data from the database manually using T-SQL.

Through my years every bit a DBA I've used many different templates and processes to handle removing data from a database, only I have found that the best way to protect yourself is to validate the number of records yous are deleting and make sure this count is correct. Many times I've seen template scripts where someone forgets to update an ID in the where clause and they delete the wrong data or they miss a condition in the where clause and delete as well much data. I've fifty-fifty seen i case when the where clause was omitted and everything was deleted.

Sample Setup

To demonstrate how our stored procedure works nosotros'll need a simple tabular array loaded with some random information. Below is the T-SQL to create this tabular array.

-- table setup CREATE TABLE Main (col1 INT chief key, col2 INT);  DECLARE @val INT SELECT @val=1 WHILE @val < 50000 Brainstorm  INSERT INTO Principal VALUES (@val,round(rand()*100000,0)); SELECT @val=@val+1; END;          

Delete SQL Server Stored Procedure

Hopefully the reasons to a higher place are enough to convince you that checking the count is a good way to help avert these issues, and so let's have a look at a stored procedure I put together that will validate the number of records nosotros remove when we are performing a delete. Here is the T-SQL to create this SQL Server stored process.

-- create procedure CREATE Procedure sp_delete_from_table (@table sysname,  @whereclause varchar(7000),  @delcnt bigint,  @actcnt bigint output) AS Begin declare @sql varchar(8000)  brainstorm transaction select @sql='delete from ' + @tabular array + ' ' + @whereclause execute(@sql) select @actcnt=@@rowcount if @actcnt=@delcnt begin print cast(@actcnt as varchar) + ' rows have been deleted.' commit transaction finish else begin print 'Statement would have deleted ' + cast(@actcnt as varchar) +  ' rows so the transaction has been rolled dorsum.' rollback transaction select @actcnt=0 finish END Get

The code for the procedure is pretty straightforward. The parameters required are the table you want to delete from, the where clause which identifies which records will be deleted and the count of records you wait to exist deleted.

The first step of the procedure begins a new transaction which volition let us to rollback afterwards if needed. It then builds and executes the delete argument based on the parameters passed to the stored procedure. Information technology uses @@rowcount after the delete executes to determine if the expected number of records were deleted. If these match then a message is printed and the transaction is committed. If the record counts don't match, then a bulletin is printed to tell you how many rows would have been deleted and the transaction is rolled dorsum.

One matter to note with the stored process is that you lot can pass in anything for the where clause. It is non checked and then you could ready this parameter to something like 'WHERE ane=one' if you wanted to delete the entire tabular array and as long as you lot pass in the correct expected delete count the records will exist deleted.

Example Usage

Now that we know how it all works permit's take a look at a couple examples.

The first stored procedure call beneath shows what would be output if the incorrect expected record count is passed to the stored procedure.

-- rollback instance declare @actcnt bigint exec sp_delete_from_table 'Principal','where col2=85942',one,@actcnt output          

And here is the output from the above example.

-- rollback output Statement would have deleted half-dozen rows and so the transaction has been rolled back.          

The second call shows what is output when the right expected count is passed and the stored procedure successfully deletes the data from the table.

-- success example declare @actcnt bigint exec sp_delete_from_table 'Main','where col2=85942',six,@actcnt output

And here is the output from the above example.

-- success output 6 rows have been deleted.

Side by side Steps

• Investigate extending the functionality of this stored procedure to include table updates
• Use triggers to prevent accidental data updates/deletes
• Read other tips on deleting information:
  • Deleting Data in SQL Server with TRUNCATE vs DELETE Commands
  • Delete Duplicate Rows with No Main Key on a SQL Server Tabular array
  • Deleting Historical Data from a Large Highly Concurrent SQL Server

Related Manufactures

Pop Articles

Nigh the author

Ben Snaidero has been a SQL Server and Oracle DBA for over ten years and focuses on functioning tuning.

View all my tips

Article Last Updated: 2016-04-20

hamiltonainal1983.blogspot.com

Source: https://www.mssqltips.com/sqlservertip/4261/sql-server-stored-procedure-to-safely-delete-data/

Share this post