SQL Stored Procedure for Common Delete

Pass Table Name and Primary key(s) parameter to delete rows [multiple rows at a time by passing multiple primary keys separated by (,) ]
alter proc [dbo].[sp_Common_Delete]
(
	@DeleteString varchar(4000),
	@DELIMITER varchar(1),
	@TableSeparator varchar(100),
	@PrimaryKey varchar(100)
)
--exec sp_Common_Delete ',6,5',',','T_Common_Department_Setup','departmentCode'
as
begin
	
	DECLARE @IDX INT,
			@Count int
			
	DECLARE @CommonId VARCHAR(8000),
			@SqlSting varchar(4000)
			
	SELECT @IDX = 1
	set @Count = 0
	
	IF (LEN(@DeleteString)<1) OR @DeleteString IS NULL 
		RETURN
		
	WHILE @IDX!= 0
	BEGIN
		SET @IDX = CHARINDEX(@DELIMITER,@DeleteString)
		IF @IDX!= 0
			SET @CommonId = LEFT(@DeleteString,@IDX-1)
		ELSE
			SET @CommonId = @DeleteString

	IF(LEN(@CommonId)> 0)
	begin
		set @SqlSting = 'delete from ' +  @TableSeparator + ' where ' + @PrimaryKey + ' = ' + @CommonId		
		EXEC (@SqlSting)	
	end	
	
	SET @DeleteString = RIGHT(@DeleteString,LEN(@DeleteString)-@IDX)
	IF LEN(@DeleteString)= 0 
		BREAK
	END
end

C# Code

private void CommonDelete()
        {
            GlobalString = "";

            try
            {
                foreach (GridViewRow rowItem in gvPreview.Rows)
                {
                    CheckBox chkDelete = (CheckBox)rowItem.FindControl("chkDelete");
                    if (chkDelete.Checked)
                    {
                        Label DepartmentCode = (Label)rowItem.FindControl("DepartmentCode");
                        GlobalString = GlobalString + "," + DepartmentCode.Text.Trim();
                    }
                }

                bll_CommonInfoDelete objbll_CommonInfoDelete = new bll_CommonInfoDelete();
                objbll_CommonInfoDelete.CommonInfoDelete(GlobalString, ",", "T_Common_Department_Setup", "departmentCode");

                if (CommonVariable.CommonId == 1)
                {
                    binddatagrid();
                    CommonMethods.SuccessMessageColor(lblMessage, "Ok");
                }
                else
                    CommonMethods.FailuerMessageColor(lblMessage, "Failed");
            }
            catch (Exception ex)
            {
                CommonMethods.FailuerMessageColor(lblMessage, ex.Message.ToString());
            }
        }
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s