Hi Friends,
I have made one simple Store procedure, which can be used to Multiple Delete operations
from Different Tables. The store procedure provides you the facility to check
the foreign constraints also. If any perticular record is in use in the given
table, then the record cannot be deleted. And if not then it will be deleted.
Actually, I have made one column called Status which contains values like, "Active",
"InActive" And "Delete".
I am not actually deleting the record from the table, but just modifying its status
to 'Delete'.
Whie executing this stored procedure, you have to pass the 6 parameters (@ID or @MultiUserID,
any one of them)
@Id is for single record and @MultiUserID is for deleting multiple records to be
deleted.
@Mode describes the mode to be passed to the Stored Procedure whether it is a single
delete or multiple delete.
@MainTable is the table from which the records are to be deleted.
@MainField is the field of the MainTable (mostly the Primary Key column).
@Tables are the table name the value of @MainField from @MainTable is used.
@Fields are the fields which are value of @Mainfield(Ie. IDs) in the other tables.
The @Fields is bit confusiong. Let me clear that. They are the IDs of Maintable used
in other table as references. (which I used)
See this Stored Procefure for more understanding.
CREATE PROCEDURE [dbo].[Proc_Multiple_Delete]
(
@MODE varchar(50)='',
@ID int = 0,
@MultiUserId varchar(255)='',
@MainTable varchar(100)='',
@MainField varchar(100)='',
@Tables varchar(1000)='',
@Fields varchar(1000)=''
)
AS
BEGIN
DECLARE @INDEX_TABLE INT
DECLARE @INDEX_FIELD INT
DECLARE @INDEX_ID INT
DECLARE @TABLENAME VARCHAR(100)
DECLARE @FIELDNAME VARCHAR(100)
DECLARE @QUERY1 VARCHAR(1000)
DECLARE @QUERY2 VARCHAR(1000)
DECLARE @MULTIID VARCHAR(500)
DECLARE @STR VARCHAR(1000)
DECLARE @ID_DEL VARCHAR(10)
SET @INDEX_TABLE = 1
SET @INDEX_FIELD = 1
SET @INDEX_ID = 1
IF (@Tables IS NULL OR @Fields IS NULL) RETURN
WHILE (@INDEX_TABLE != 0 AND @INDEX_FIELD != 0)
BEGIN
SELECT @INDEX_TABLE = CHARINDEX(',', @Tables)
SELECT @INDEX_FIELD = CHARINDEX(',', @Fields)
IF (@INDEX_TABLE != 0 AND @INDEX_FIELD != 0)
BEGIN
SELECT @TABLENAME = LEFT(@Tables, @INDEX_TABLE-1)
SELECT @FIELDNAME = LEFT(@Fields, @INDEX_FIELD-1)
END
ELSE
BEGIN
SELECT @TABLENAME = @Tables
SELECT @FIELDNAME = @Fields
END
--PRINT(@TABLENAME + ' ' + @FIELDNAME)
SET @QUERY1 = 'SELECT ' + @MAINFIELD + ' FROM ' + @MAINTABLE + ' WHERE ' + @MAINFIELD + ' = ' + Convert(Varchar(20),@ID)
SET @QUERY2 = 'SELECT ' + @FIELDNAME + ' FROM ' + @TABLENAME + ' WHERE Status <> ''Delete'' '
--PRINT(@QUERY1)
--PRINT(@QUERY2)
CREATE TABLE #ID(SINGLE_ID INT)
INSERT #ID EXEC(@QUERY1)
CREATE TABLE #MULTIPLEID(MULTI_ID INT)
INSERT #MULTIPLEID EXEC(@QUERY2)
--SELECT SINGLE_ID FROM #ID
IF @MODE='DELETE'
BEGIN
IF EXISTS(SELECT SINGLE_ID FROM #ID)
BEGIN
IF @ID NOT IN (SELECT MULTI_ID FROM #MULTIPLEID)
BEGIN
EXEC('UPDATE '+ @MAINTABLE + ' SET Status=''Delete'' WHERE ' + @MAINFIELD + ' = ' + @ID)
SELECT @ID AS RETURNVAL
END
ELSE
BEGIN
SELECT 0 AS RETURNVAL
END
END
ELSE
BEGIN
SELECT 0 AS RETURNVAL
END
END
IF @MODE='MULTIDELETE'
BEGIN
IF @MultiUserID <> ''
BEGIN
WHILE(@INDEX_ID != 0)
BEGIN
SELECT @INDEX_ID = CHARINDEX(',',@MULTIUSERID)
IF @INDEX_ID != 0
SELECT @ID_DEL = LEFT(@MULTIUSERID, @INDEX_ID-1)
ELSE
SELECT @ID_DEL = @MULTIUSERID
IF @ID_DEL NOT IN (SELECT MULTI_ID FROM #MULTIPLEID)
BEGIN
EXEC('UPDATE ' + @MAINTABLE + ' SET Status = ''Delete'' WHERE ' + @MAINFIELD + ' = ' + @ID_DEL)
SELECT 1 AS RETURNVAL
END
ELSE
SELECT 0 AS RETURNVAL
SET @MULTIUSERID = RIGHT(@MULTIUSERID, LEN(@MULTIUSERID)-@INDEX_ID)
IF LEN(@MULTIUSERID) = 0 BREAK
END
END
ELSE
SELECT 0 AS RETURNVAL
END
-- Upto Here
SELECT @Tables = RIGHT(@Tables, LEN(@Tables)- @INDEX_TABLE)
SELECT @Fields = RIGHT(@Fields, LEN(@Fields)- @INDEX_FIELD)
IF(LEN(@Tables)=0 or LEN(@Fields)=0) BREAK
END
END
See the following code how this stored procedure is Executing.
EXEC Proc_Multi_Delete 'MULTIDELETE',0,'1,9','BookMaster','BookID','BookAllocation','BookID'
Here we are passing two IDs for books to be deleted from BookMaster table which has
main field (i.e. Primary Key) as BookId.
The another table is BookAllocation table, in which the BookID of BookMaster is used
as reference as the
same name ie. BookID. But in some cases the reference may not have the same name
of the coluom, thats why we have to pass that field name in @Fields.
So the Proc will check the BookID 1 and 9 whether they are presenet in BookAllocation
table or not. If they
are present, it will return 0 value and if they are not used in BookAllocation table,
the Stored Procedure will
set their status as 'Delete'.
Please contact me for any query related to this article. This is most useful query,
which I use in my all projects. Once you understand this, you will have much
benifit of this.