Naveen J V (4/20/2015)

---

Hi Experts,

I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.

Link:

http://stackoverflow.com/questions/485581/generate-delete-statement-from-foreign-key-relationships-in-sql-2008

--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child

--When I am deleting a grand child table, it should be linked to child table first followed by Parent

--- query 1

DELETE Top(100000) FROM [dbo].[ChartInstanceCase]

FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID]

INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId]

WHERE [Patient].PracticeID = '55';

--Query 2

DELETE Top(100000) [dbo].[ChartInstanceCase]

FROM [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID]

INNER JOIN [dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID]

WHERE [Patient].PracticeID = '55';

Please do let me know how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2

Please help.

Thanks,

Naveen