Stored Procedure For A Soft Delete

Question

Stored Procedure For A Soft Delete

sharpcnet

SSC Veteran

Points: 246
More actions
December 22, 2013 at 6:23 am

#283917

This code is to perform a Soft Delete. It deletes a record first. If the delete was successful, rollsback and then updates the flag column of the same record. If that record has dependencies and could not be deleted, does nothing. In order to know, the action happened, I kept the Status variable. But, it always results as null, no matter what. Where am I going wrong.
ALTER PROCEDURE SoftDelete
(
@TableName nvarchar(50), @ColName nvarchar(50),
@Id nvarchar(50)
)
AS
BEGIN
DECLARE @qryDel nvarchar(MAX),@qryUpd nvarchar(MAX),@Status int = null,
@Param nvarchar(MAX)
SET @Param = '@TableName nvarchar(50), @ColName nvarchar(50),
@Id nvarchar(50)'
SET @qryDel = N'delete from @tablename where @colname=@id'
SET @qryUpd = N'update @tablename set deleted = 1 where @colname=@id'
BEGIN TRY
BEGIN TRANSACTION
EXECUTE sp_executesql @qryDel, @Param, @TableName, @ColName, @Id
ROLLBACK TRANSACTION
BEGIN TRANSACTION
EXECUTE sp_executesql @qryUpd, @Param, @TableName, @ColName, @Id
COMMIT TRANSACTION
SET @status = 1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SET @status = 0
END CATCH
SELECT @status
END
C#:(Linq To Entities)
public int SoftDelete()
{
return MYDB.SoftDelete("tblCountry","CountryId,"101").FirstOrDefault ?? -1;
}

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

sharpcnet (12/22/2013)
This code is to perform a Soft Delete. It deletes a record first. If the delete was successful, rollsback and then updates the flag column of the same record. If that record has dependencies and could not be deleted, does nothing. In order to know, the action happened, I kept the Status variable. But, it always results as null, no matter what. Where am I going wrong.
ALTER PROCEDURE SoftDelete
(
@TableName nvarchar(50), @ColName nvarchar(50),
@Id nvarchar(50)
)
AS
BEGIN
DECLARE @qryDel nvarchar(MAX),@qryUpd nvarchar(MAX),@Status int = null
SET @qryDel = 'delete from'+@tablename+' where '+@colname+'='+@id
SET @qryUpd = 'update '+@tablename+' set deleted = 1 where '+@colname+' = '+@id
BEGIN TRY
BEGIN TRANSACTION
EXECUTE sp_executesql @qryDel
ROLLBACK TRANSACTION
BEGIN TRANSACTION
EXECUTE sp_executesql @qryUpd
COMMIT TRANSACTION
SET @status = 1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SET @status = 0
END CATCH
SELECT @status
END
C#:(Linq To Entities)
public int SoftDelete()
{
return MYDB.SoftDelete("tblCountry","CountryId,"101").FirstOrDefault ?? -1;
}

Try changing SELECT @status to RETURN @status

sharpcnet SSC Veteran Points: 246 More actions · Answer 2

Should I have an output parameter then? I declared one.. @status int out.

When I execute the SP from SS2008, it gives two results - @status - 0 ; Return Value - 0. - For every record.

Updated my query . please have a look

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Unfortunately, this is a duplicate post. No more replies on this thread, please. Unless I missed my guess, this problem has been solved at the following thread.

http://www.sqlservercentral.com/Forums/Topic1525325-392-1.aspx

@sharpcnet,

Please don't double post. It just splits up people's thoughts and possible answers. Most of the heavy hitters monitor all posts no matter which forum they're in so no need to post on multiple forums ,never mind posting the same question on the same forum more than once.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)