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)
