Delete product by identifier
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- [dbo].[DeleteProductById] 123
Create PROCEDURE [dbo].[DeleteProductById1]
(
@ProductId int
)
AS
BEGIN
CREATE TABLE #tmp_Orders (Id integer)
CREATE TABLE #tmp_Pictuers (Id integer)
delete FROM ShoppingCartItem Where ProductId = @ProductId
insert into #tmp_Orders select OrderId from OrderItem where ProductId = @ProductId
delete from #tmp_Orders
delete from OrderItem where ProductId = @ProductId
delete FROM OrderNote where OrderId in (Select Id from #tmp_Orders)
delete from [Order] where id in (select orderID from OrderItem where productId = @ProductId)
delete FROM RecurringPayment where InitialOrderId in (select orderID from OrderItem where productId = @ProductId)
delete FROM RewardPointsHistory where UsedWithOrder_Id in (select orderID from OrderItem where productId = @ProductId)
delete FROM [Order] where Id in (Select Id from #tmp_Orders)
delete FROM ProductAttributeCombination Where ProductId = @ProductId
delete FROM RelatedProduct where ProductId1 = @ProductId
delete FROM ProductAttributeValue where AssociatedProductId = @ProductId
delete FROM Product_Category_Mapping where ProductId = @ProductId
delete FROM Product where id = @ProductId -- order by ID desc
insert into #tmp_Pictuers select PictureId from Product_Picture_Mapping where ProductId = @ProductId
delete FROM Product_Picture_Mapping where ProductId = @ProductId
delete FROM Picture where id in (select Id from #tmp_Pictuers) --order by ID desc
delete FROM Product_ProductAttribute_Mapping Where ProductId = @ProductId
delete FROM Product_ProductTag_Mapping Where Product_Id = @ProductId
delete FROM Product_SpecificationAttribute_Mapping Where ProductId = @ProductId
delete FROM ProductReviewHelpfulness where ProductReviewId in (select id FROM ProductReview Where ProductId = @ProductId)
delete from UrlRecord Where EntityName = 'Product' and EntityId = @ProductId
delete from Product where Id = @ProductId
DROP TABLE #tmp_Orders
DROP TABLE #tmp_Pictuers
END