Delete record efficiently. (sp_CheckAndDelete )
The sp_CheckAndDelete stored procedure specially created for handling process of data deleting efficiently in MS SQL Server 2000 tables, as it is too difficult to handle this process when dependants exist for record to be deleted. When the deleting process is done with sp_CheckAndDelete stored procedure, you will be informed the status of the process whether it is successful or not.
The table involves in the process must have a primary key.
IF OBJECT_ID('sp_CheckAndDelete') IS NOT NULL
DROP PROCEDURE sp_CheckAndDelete
GO
CREATE PROCEDURE sp_CheckAndDelete (@TableName varchar(50), @TableOwner varchar(50), @PrimaryKeyValue varchar(100),
@IsPrimaryKeyValueTypeString bit, @RetMsg tinyint output)
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
DECLARE @f_quqlifier sysname,
@f_owner sysname,
@f_tablename sysname,
@f_columnname varchar(100),
@strsql varchar(500),
@p_quqlifier sysname,
@p_owner sysname,
@p_tablename sysname,
@p_columnname sysname
SET @RetMsg = 0
CREATE TABLE #fkeys
(p_quqlifier sysname,
p_owner sysname,
p_tablename sysname,
p_columnname sysname,
f_quqlifier sysname,
f_owner sysname,
f_tablename sysname,
f_columnname varchar(100),
key_seq smallint,
updaterule smallint,
deleterule smallint,
fk_name sysname,
pk_name sysname,
diff sql_variant)
-- Collecting all foreign key information
INSERT INTO #fkeys EXEC sp_fkeys @TableName, @TableOwner
--*************************************************************************************************************
--Checking whether any dependants exist.
--*************************************************************************************************************
DECLARE cur_fkeys CURSOR
FOR
SELECT p_quqlifier, p_owner, p_tablename, p_columnname, f_quqlifier, f_owner, f_tablename, f_columnname FROM #fkeys
OPEN cur_fkeys
FETCH NEXT FROM cur_fkeys INTO @p_quqlifier, @p_owner, @p_tablename, @p_columnname, @f_quqlifier, @f_owner, @f_tablename, @f_columnname
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @strsql = 'SELECT TOP 1 ' + @f_columnname + ' AS [88786] FROM '
SET @strsql = @strsql + @f_quqlifier + '.' + @f_owner + '.' + @f_tablename
IF (@IsPrimaryKeyValueTypeString=1)
SET @strsql = @strsql + ' WHERE ' + @f_columnname + ' = ' + CHAR(39) + @PrimaryKeyValue + CHAR(39)
ELSE
SET @strsql = @strsql + ' WHERE ' + @f_columnname + ' = ' + @PrimaryKeyValue
EXEC (@strsql)
-- If records exist, should stop the execution and make the return values as "Dependant exist. cannot delete".
IF @@ROWCOUNT > 0
BEGIN
SET @RetMsg = 1
BREAK
END
FETCH NEXT FROM cur_fkeys INTO @p_quqlifier, @p_owner, @p_tablename, @p_columnname, @f_quqlifier, @f_owner, @f_tablename, @f_columnname
END
IF (@RetMsg = 1)
RETURN 1
--*************************************************************************************************************
--Checking whether the record exist in the table
--*************************************************************************************************************
SET @strsql = 'SELECT ' + @p_columnname + ' FROM '
SET @strsql = @strsql + @p_quqlifier + '.' + @p_owner + '.' + @p_tablename
IF (@IsPrimaryKeyValueTypeString=1)
SET @strsql = @strsql + ' WHERE ' + @p_columnname + ' = ' + CHAR(39) + @PrimaryKeyValue + CHAR(39)
ELSE
SET @strsql = @strsql + ' WHERE ' + @p_columnname + ' = ' + @PrimaryKeyValue
EXEC (@strsql)
-- if no records, make the return parameter as "Record not exist"
IF (@@ROWCOUNT < 1)
BEGIN
SET @RetMsg = 2
RETURN 1
END
--*************************************************************************************************************
--Deleting record.
--*************************************************************************************************************
SET @strsql = 'DELETE FROM '
SET @strsql = @strsql + @p_quqlifier + '.' + @p_owner + '.' + @p_tablename
IF (@IsPrimaryKeyValueTypeString=1)
SET @strsql = @strsql + ' WHERE ' + @p_columnname + ' = ' + CHAR(39) + @PrimaryKeyValue + CHAR(39)
ELSE
SET @strsql = @strsql + ' WHERE ' + @p_columnname + ' = ' + @PrimaryKeyValue
EXEC (@strsql)
-- make the return parameter as "Deleted"
SET @RetMsg = 3
SET NOCOUNT OFF
END 
