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

public int SoftDelete()
{
  return MYDB.SoftDelete("tblCountry","CountryId,"101").FirstOrDefault ?? -1;
}

quote:

---

Originally posted by sharpcnet

Should I have an output parameter then? I dteclared one @Status int out.
When I execute the SP from SS2008, it gives two results - @Status - 0 ; Return Value - 0. - For every record.

---