Stored Procedure For A Soft Delete

Question

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 = N'@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;
}

What is your database's current transaction isolation level? See this post for easy way to determine http://stackoverflow.com/questions/1038113/how-to-find-current-transaction-level — Jon Jaussi, Dec 22 '13 at 14:29
Sorry, but that was too far for my knowledge. I dont think, I might be able to help you with some needed answers.Thank you though — Ruby, Dec 22 '13 at 15:03
All your variables are of type `NVARCHAR` - and to indicate that you're using Unicode strings, you should prefix those string literals with a `N` ! Use this: `SET @qryDel = N'delete from @tablename where @colname=@id'` to be precise and clear — marc_s, Dec 22 '13 at 15:54
@Ruby: the `@Param` don't seem to have a `N` prefix yet (at least in your post) — marc_s, Dec 22 '13 at 17:06
It was just in this post. This seems like simple but...,Is it working at your end. I tried making a fiddle but was unable to create foreign key there. — Ruby, Dec 23 '13 at 06:16

score 6 · Answer 1 · answered Dec 22 '13 at 14:48

6

You're just missing spaces...

Given this:

'delete from'+@tablename+'

when you're trying to delete from tblCountry, you'll get

delete fromtblCountry

You need to add a space between the from keyword and the table variable!

Use this:

'delete from '+@tablename+'
            ^
            +------- crucial **SPACE** here!!

But WHY OH WHY are you first actually deleting the row, rolling back that transaction, and only then you're doing the "soft delete" ? Doesn't make any sense to me....

answered Dec 22 '13 at 14:48

marc_s

732,580
175
1,330
1,459

My suspicion is that @ruby wants the SQL Engine to do the check for delete conflicts (FKs, etc.) for them, so they can know if the soft delete is valid for "soft" relational integrity. It's a *clever* trick, which means it could be dangerous: I'm not sure what happens if there's a containing transaction (since SQL Server does not support "true" nested transactions). – RBarryYoung Dec 22 '13 at 14:56
Exactly, but I have no idea about the nested transactions. Could you give a simple eg: – Ruby Dec 22 '13 at 15:05
Also agree with marc_s, but at this level, I found this simpler. – Ruby Dec 22 '13 at 15:06
Its now giving 0 for all cases – Ruby Dec 22 '13 at 15:08
@Ruby: you'll probably also have to put the `@id` into single quotes, since it's a `nvarchar` - or better yet: **use parametrized queries!** – marc_s Dec 22 '13 at 15:09
Point. I have updated my query but it still's giving 0. Please have a look. – Ruby Dec 22 '13 at 15:26
3

@Ruby: For the inner transactions of nested transactions, the COMMITs are ignored and unnamed ROLLBACKs will rollback *all* of the nested transactions. If you name your inner transaction here, you might be able to get it to work correctly when nested (not sure). See here: http://technet.microsoft.com/en-us/library/ms189336(v=sql.105).aspx – RBarryYoung Dec 22 '13 at 15:43
@RBarryYoung Just wondering what is the real point of this? I can check if a record can be deleted and mark it with flag `1` and after this a record ro be inserted in other table which will cause the future delete to fail. – gotqn Dec 22 '13 at 22:15
1

@gotqn You'd have to ask Ruby to be sure, but presumably on the Insert, they check for that also (i.e., don't allow the Insert if this record is flagged for Delete). As for what the point of all this is, my guess is that they are keeping "deleted" records for history/archive purposes. – RBarryYoung Dec 22 '13 at 23:03
That's right. For Archives. Lets say there is a 'city' form which has a 'countries' dropdown which will load only the non flagged ones. So insert of a city with a deleted country is ruled out. Works for us. – Ruby Dec 23 '13 at 06:18

Stored Procedure For A Soft Delete

1 Answers1