tblParent pid(int) name deleted(bit)
1 abc 0
2 def 0
tblChild cid(int) name pid(ForeignKey)
1 aaa 1
2 bbb 1
When a record from tblParent is being deleted, it should check for any child records. If yes, rollback & return 0. If no, then update the 'deleted' column to '1' and return 1. Basically, doing a soft delete.
The SP works fine. All I need is to know a value as 0 or 1 based upon the action that took place. How should it be done. I would call this store procedure from c#, linq to entities to get the status. something like:
C#: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
ERROR: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1