Rather than creating a procedure, I'd suggest you create a trigger on parent's table. AFAIK, that's the most trusted way to ensure that the child rows would be deleted whenever the parent row is deleted. And it respect transaction by default :)
hmm.... Okay. Now, you wrote:
i made an sp that works delete both parent and child records i want but now the problem is testing the sp as i m unable to lock records lets consider a scenario in which record from parent contains three records in child now if i lock one of the recorfd then the delete transaction shud b rollback so in this case how to lock one of the row from child record please help me for that.
So, as far as I understand (please cmiiw), you already created the SP, but you want the SP to cancel the deletion if any of its child is locked or being edited. Right? Ok. How do you 'lock' the child row? I think it's better if you add one column on the child -- say, IsLocked (bit) -- and set it to 1 if the row is 'in edit mode'. If the the apps already not in the edit mode, then the apps should update the bit to 0. On the delete SP, you can check if there is/are any child that has IsLocked turn on. If it is, then skip the deletion. If not (all child's IsLocked is off) then the SP can do the deletion. I mean something like this:
If not exists (Select * from child where IsLocked = 1)
Begin
delete from parent
where key_col = @Key_of_row_tobe_deleted
delete from child
where foreign_key = @Key_of_row_tobe_deleted
End
hth,
foxyland