Multi Delete Store Procedure

This article describes the stored procedure to delete single as well multiple records from the table also it takes care of relations with other tables. This is very beneficial while deleting multiple records from DataGrid or Gridview.

 

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.

By Shailendrasinh Parmar   Popularity  (2293 Views)
Picture
Biography - Shailendrasinh Parmar