Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The SQL Delete Statement

By Narayana Raghavendra,

MERGE Statement  We can find a lot of stuff on SQL Select, Insert and etc.  There are few articles, discussions, developers R&D on SQL DELETE.  Here is an idea about the SQL DELETE statement. If the data is deleted from the tables without using Transactions, there is no way for Developers to retrieve it back, probably DBAs or certain tools may retrieve it back for you.  Here is an effort to give a little leverage to Developers in dealing with record deletion. This Stored Procedure accepts DELETE SQL statement and comment for deletion as parameters.  Copies the deleting records to another table with the same name but prefixed by Recycle.  Along with copying the deleting records, it stores two more information for back reference, i.e. comment for deleting the record, incremental number that uniquely identifies a record or set of records that got deleted in each DELETE SQLs.

Algorithm

  • Accept DELETE SQL Statement, Comment to be put to all the deleting records.
  • Check for the DELETE key word in DELETE Sql parameter
  • Construct the Select/Insert SQL statement in order to insert the deleting rows using SELECT INTO or INSERT..SELECT statements. 
  • Execute the constructed Insert/Select SQL.
    • If the Recycle copy of the deleting records table does not exist then create one.
    • Insert all the deleting records to recycle copy, with Transaction Number and Comment i.e. supplied by the Developer.
  • Finally execute the actual SQL DELETE.

Expansion

You can store more information about the deleting rows by adding more columns in Recycle table like Database Login ID, Deleted Date and Time, DELETE SQL Statement and etc. depending on the requirement.

SP Script

CREATE PROCEDURE SP_RDELETE
(@SDELETE AS VARCHAR(2000),   --DELETE SQL STATEMENT AS PARAMETER

@SCOMMENT AS VARCHAR(1000),   --COMMENT/TITLE FOR THE DELETING RECORD(S)

@ERRORMESSAGE AS VARCHAR(2000) --ERROR MESSAGE IF ANY DURING THE
SP EXECUTION
)
  AS

--VARIABLE DECLARATION

DECLARE @SSTR AS VARCHAR(2000),    
--VARIABLE TO CONSTRUCT SQL STATEMENTS

@SSELECT AS VARCHAR(2000),    --VARIABLE TO
CONSTRUCT 'SELECT' SQL

@STABLE AS VARCHAR(2000),     --VARIABLE TO
STORE TABLE NAME

@SPOS AS
INT                 
--VARIABLE TO STORE CHARACTER POSITIONS

--TRIMMING OF DATA

SET @SDELETE = ' '+ LTRIM(RTRIM(@SDELETE)) + ' '

--COPY OF @SDELETE PARAMETER TO EXECUTE 'SELECT' SQL
STATEMENT

SET @SSELECT = @SDELETE

--CONSTRUCT 'SELECT' SQL FROM @SDELETE PARAMETER

--USING 'SELECT' SQL COPY THE DELETING RECORDS TO RECYCLE TABLE
COPY

SET @SPOS = CHARINDEX(' DELETE ', @SSELECT)

IF @SPOS > 0

BEGIN

      IF CHARINDEX(' FROM ', @SSELECT,
@SPOS) = 0

      BEGIN

          
SET @STABLE = LTRIM(RTRIM(SUBSTRING(@SSELECT, @SPOS + 7,
2000)))
           
SET @SSELECT = ' SELECT * FROM ' + @STABLE
      END
      ELSE
      BEGIN
           
SET @SSTR = RTRIM(LTRIM(SUBSTRING(@SSELECT, CHARINDEX(' FROM ',
@SSELECT) + 6, 2000)))

          
IF CHARINDEX(' ', @SSTR) = 0
           
BEGIN
                 
SET @SSELECT = ' SELECT * FROM ' + @SSTR              
SET @STABLE = @SSTR

           
END
           
ELSE
           
BEGIN
                 
SET @STABLE = SUBSTRING(@SSTR, 1, CHARINDEX(' ', @SSTR))
                 
SET @SSELECT = REPLACE(@SSELECT, ' DELETE ', ' SELECT * ')
           
END

      END

END

ELSE

BEGIN

      SET @ERRORMESSAGE = 'NO DELETE
KEYWORD IN THE SDELETE PARAMETER'

      RETURN -1

END

--USING 'SELECT' SQL COPY THE DELETING RECORDS TO RECYCLE TABLE
COPY

SET @STABLE = 'RECYCLE_' + @STABLE

--CHECK WHETHER RECYCLE TABLE FOR DELETING RECORDS TABLE ALREADY
EXIST

--IF DOES NOT EXIST CREATE ONE

IF (SELECT 1 FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = @STABLE ) > 0

BEGIN      

      --TABLE ALREADY EXIST

      --BUILDING THE SQL: INSERT INTO
EXISTING RECYLE TABLE

--USING 'INSERT..SELECT' SQL

      SET @SSELECT = REPLACE (@SSELECT,
' SELECT * ', ' INSERT INTO ' + @STABLE + ' SELECT *, (SELECT
MAX(TRANSNO)+1 FROM ' + @STABLE + '), ''' +  @SCOMMENT + '''
')

END

ELSE  --TABLE DOES NOT EXIST

BEGIN

      --BUILDING THE SQL: CREATE TABLE
USING 'SELECT INTO' SQL

      SET @SSELECT = REPLACE (@SSELECT,
' SELECT * ', ' SELECT *, 1 TRANSNO, ''' + @SCOMMENT + ''' COMMENT
INTO ' + @STABLE + ' ' )

END

BEGIN TRANSACTION TRANS_SP_DELETE

--EXECUTE THE CONSTRUCTED SELECT SQL THAT CREATES/INSERTS
RECYCLE TABLE

EXEC(@SSELECT)

--CHECK FOR ERRORS

IF @@ERROR > 0

BEGIN

      SET @ERRORMESSAGE = 'ERROR WHILE
CREATING/UPDATING RECYCLE TABLE'

      ROLLBACK TRANSACTION
TRANS_SP_DELETE

      RETURN -1

END

--EXECUTE THE ACTUAL DELETE STATEMENT NOW

EXEC (@SDELETE)

--CHECK FOR ERRORS

IF @@ERROR > 0

BEGIN

      SET @ERRORMESSAGE = 'ERROR WHILE
EXECUTING PASSED DELETE SQL STATEMENT'

      ROLLBACK TRANSACTION
TRANS_SP_DELETE

      RETURN -1

END

ELSE

BEGIN

      --COMPLETED SUCCESSFULLY

      COMMIT TRANSACTION
TRANS_SP_DELETE

      RETURN 0

END

Parameters:

Parameter Name

Description

@SDELETE

Actual DELETE SQL statement

@SCOMMENT

Comment/Title for the deleting records.  This will be stored in “Comment” column of Recycle copy of the table.
@ErrorMessage  [INPUT/OUTPUT Parameter] Any error during the SP execution.

Returns

  • Returns 0 on successful execution.
  • Returns -1 on unsuccessful execution with error message in @ErrorMessage input/output parameter

Limitation of SP

Currently the SP is designed to handle only certain kind of SQL DELETE statements. The supported SQL DELETE types are below:
  • Use DELETE with no parameters E.g. DELETE authors OR DELETE from authors
  • Use DELETE on a set of rows E.g. DELETE FROM authors WHERE au_lname = 'McBadden'
Effort is on to include all type of DELETE statements, some of the other types of DELETE statements are
  • Use DELETE on the current row of a cursor
  • Use DELETE based on a subquery or use the Transact-SQL extension
  • Use DELETE and a SELECT with the TOP Clause

Example

This example shows how the records are deleted/archived. The Employees table contains 8 records.

All the employees who were hired before 1st January, 2000 will be deleted from Employees table and stored under Recycle_Employees table after executing the SP.

Five Records copied to Recycle table that has Hire Date lesser than 1-Jan-2000.

Usage

  • While fixing the Production related issues, use this SP instead of DELETE SQL to keep the history of existing/bad records.
  •  To recycle the deleted records in future.
  • To Archive the old OR very rarely used records, thereby increasing the performance of the table transactions.

Send your valuable suggestions to N. RAGHAVENDRA (Raghavendra.Narayana@tfn.com)

Total article views: 19423 | Views in the last 30 days: 8
 
Related Articles
FORUM

Delete certain records in table

Delete certain records returned by select statements

FORUM

delete records

delete records

FORUM

Delete Records

Pls help to complete this delete statement

FORUM

Deleting Duplicate Record in Production

Deleting Duplicate Record in Production

FORUM

select statements

select statements

Tags
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones