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;

}