How to delete rows from multiple tables using stored procedures in SQL Server

Last Reply on Apr 06, 2015 09:13 AM By Shashikant

Posted on Apr 06, 2015 09:09 AM

How to delete row from two table using stored procedure

 

alter PROCEDURE [dbo].[deleteproductdetail]

@Product_Id int
AS
BEGIN
     
       delete from product where Product_Id=@Product_Id;
       delete from size where Product_Id=@Product_Id;
     
END

error come

Msg 547, Level 16, State 0, Procedure deleteproductdetail, Line 8
The DELETE statement conflicted with the REFERENCE constraint "FK_tbl_Size_tbl_product". The conflict occurred in database "Dresswaladbnew", table "dbo.tbl_Size", column 'Product_Id'.
The statement has been terminated.

only one table data can delete

Posted on Apr 06, 2015 09:13 AM Modified on on Apr 06, 2015 09:13 AM

You just change your script as below

SQL

CREATE PROCEDURE [deleteproductdetail]
   @Product_Id INT
AS
BEGIN
	DELETE FROM size WHERE Product_Id = @Product_Id;
	DELETE FROM product WHERE Product_Id = @Product_Id; 
END

I hope this will help you out.