STORED PROCEDURE in order to DELETE rows

You can set CASCADE delete through the foreign key relationship.

You can set the Delete/Update Rule to Cascade for the foreign key. In SQL Server Management Studio, Click on your table name and find the Keys and expand i;, right click on the foreignkey name in your key collection and click Modify>> under INSERT and UPDATE specification to see the Delete Rule and Update Rule. The default value is No Action for DELETE Rule or UPDATE Rule. You need to change the value to Cascade.