pid(int) name deleted(bit)
1        abc   0
2        def   0

cid(int) name  pid(ForeignKey)
1        aaa   1
2        bbb   1

public int somefuntion() //returning a string is also fine..
   {
     return MYDB.SoftDelete(parameters.....);
   }

ALTER PROCEDURE SoftDelete
(
  @TableName nvarchar(50), @ColName nvarchar(50),
  @Id nvarchar(50)
)
AS
BEGIN
  DECLARE @qry nvarchar(500)

  SELECT @qry = 'begin transaction
                 delete '+@tablename+' where '+@colname+'='+@id+'
                 if(@@Error <> 0)
                  Begin
                    --select 0
                  End
                 else
                  Begin
                   rollback transaction
               update '+@tablename+' set deleted = 1 where '+@colname+' = '+@id+'
                   --select = 1
                  end'

   EXECUTE sp_executesql @qry
END

ALTER PROCEDURE SoftDelete
(
  @TableName nvarchar(50), @ColName nvarchar(50),
  @Id nvarchar(50)
)
AS
BEGIN
  DECLARE @qry nvarchar(500)

  SELECT @qry = 'begin transaction
                 delete '+@tablename+' where '+@colname+'='+@id+'
                 if(@@Error <> 0)
                  Begin
                    return 0
                  End
                 else
                  Begin
                   rollback transaction
               update '+@tablename+' set deleted = 1 where '+@colname+' = '+@id+'
                   return 1
                  end'

   EXECUTE sp_executesql @qry
END

DECLARE @Ret int
EXEC @ret = SoftDelete ....
--the below will give you return status
SELECT @Ret 

quote:

---

Originally posted by sharpcnet

Just because the same function can be used for any table.

The DECLARE @Ret comes after the 'END'..right. It gave these errors:

A return statement with a return value cannot be used in this context.
Maximum stored procedure, function, trigger, or view nesting level exceeded(limit 32).

---