SQL Server Stored Procedure to Safely Delete Data


By:   |   Updated: 2016-04-20   |   Comments (4)   |   Related: More > T-SQL


Development Best Practices for SQL Server

Free MSSQLTips Webinar: Development Best Practices for SQL Server

Attend this webinar to learn about development best practices for SQL Server. Andy Warren will share his many years of experience to give some pointers on what has worked best for him and how you can utilize some of this knowledge.


Problem

In my last tip, Best Practices for Deleting SQL Server Data, I showed some examples of how you can get into trouble when deleting data from the backend database. I also mentioned in the next steps that there are some ways you can protect yourself when you do have to delete data this way. This tip will give you one method for ensuring you are deleting the number of records you are expecting to be deleted.

Solution

When it comes to best practices I am always in favor all DML changes being made through an application and not being done directly against the database using T-SQL commands. When done this way we can ensure that the code has been tested to validate that it is going to do what it is intended to do. That said, there are always cases that come up, whether it be from a bug in an application adding/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 as a DBA I've used many different templates and processes to handle removing data from a database, but I have found that the best way to protect yourself is to validate the number of records you 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 too much data. I've even seen one case when the where clause was omitted and everything was deleted.

Sample Setup

To demonstrate how our stored procedure works we'll need a simple table loaded with some random data. Below is the T-SQL to create this table.

-- table setup
CREATE TABLE Main (col1 INT primary key, col2 INT);

DECLARE @val INT
SELECT @val=1
WHILE @val < 50000
BEGIN 
INSERT INTO Main VALUES (@val,round(rand()*100000,0));
SELECT @[email protected]+1;
END;

Delete SQL Server Stored Procedure

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

-- create procedure
CREATE PROCEDURE sp_delete_from_table (@table sysname, 
@whereclause varchar(7000), 
@delcnt bigint, 
@actcnt bigint output) AS
BEGIN
declare @sql varchar(8000)

begin transaction
select @sql='delete from ' + @table + ' ' + @whereclause
execute(@sql)
select @[email protected]@rowcount
if @[email protected]
begin
print cast(@actcnt as varchar) + ' rows have been deleted.'
commit transaction
end
else
begin
print 'Statement would have deleted ' + cast(@actcnt as varchar) + 
' rows so the transaction has been rolled back.'
rollback transaction
select @actcnt=0
end
END
GO

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 expect to be deleted.

The first step of the procedure begins a new transaction which will allow us to rollback later if needed. It then builds and executes the delete statement based on the parameters passed to the stored procedure. It 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 message is printed to tell you how many rows would have been deleted and the transaction is rolled back.

One thing to note with the stored procedure is that you can pass in anything for the where clause. It is not checked so you could set this parameter to something like 'WHERE 1=1' if you wanted to delete the entire table and as long as you pass in the correct expected delete count the records will be deleted.

Example Usage

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

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

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

And here is the output from the above example.

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

The second call shows what is output when the correct 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',6,@actcnt output

And here is the output from the above example.

-- success output
6 rows have been deleted.
Next Steps


Last Updated: 2016-04-20


get scripts

next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips



Comments For This Article




Monday, October 23, 2017 - 11:12:00 PM - DVA74020475 Back To Top (68714)

I like the idea/concept of stored procedure to handle this. Given the risk with sql injection, what are other way to handle this with a front end and sql at the back end, can someone elaborate on this?

Key features I'm looking for: 

1. Inputs: (a) Update/Delete/Insert Query, (b) Expected counts, (c ) Issue ID

2. Output: (a) Counts Match - Back up data prior to update, Update Executed, (b) Counts did not match - Update not executed, (c) Write Inputs to a log for tracking purpose

 

Thanks!

 


Thursday, April 21, 2016 - 12:13:43 PM - Brain2000 Back To Top (41295)

I follow a very unconventional route.  Server Side Cursors.  Yes, you heard me correctly.  I reverse engineered the ADODB COM library and rewrote it in managed .NET code with server side cursors stored procedures included.  To me it is the safest way to run updates and deletes, because the row you're on is the row that is changed.  You don't even need a primary key.


Thursday, April 21, 2016 - 8:54:39 AM - Ben Snaidero Back To Top (41294)

Hi Elliot

Thanks for the comment.  True this stored procedure does open you to SQL injection and you could add more safeguards within the stored procedure to protect against this but that said this stored procedure should only be accessible by a DBA and not from every user account.  I should have made that more clear in my tip.  It's should only be used by DBAs/App admins to protect themselves from typos when performing deletes directly from the backend.  In no way am I suggesting that you would use a stored procedure like this to be called from application code.

Thanks for reading


Wednesday, April 20, 2016 - 8:50:46 AM - Elliot Back To Top (41283)

 Erm, SQL injection, cough, cough? I hope that's not on a prod server.

 



download





Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

Find MAX value from multiple columns in a SQL Server table

SQL Server Loop through Table Rows without Cursor














get free sql tips
agree to terms