pid(int) name deleted(bit)
1 abc 0
2 def 0
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 the status 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:
public int somefuntion() //returning a string is also fine..
ALTER PROCEDURE SoftDelete
@TableName nvarchar(50), @ColName nvarchar(50),
DECLARE @qry nvarchar(500)
SELECT @qry = 'begin transaction
delete '+@tablename+' where '+@colname+'='+@id+'
if(@@Error <> 0)
update '+@tablename+' set deleted = 1 where '+@colname+' = '+@id+'
--select = 1
EXECUTE sp_executesql @qry
Please also note that, there could be several child tables using pid as FK.