stored procedures - How can I keep tblPurchase and tblProductStock table without drop. (I need keep both table and value permanent without drop)

Question

How to change this stored procedure without drop tblPurchase and tblProductStock. When I run my program with this stored procedure after adding new record table and data will be drop. I want to keep all table and data protected. please help me to resolve that.

https://stackoverflow.com/a/65774309/13338320

Answer 1

Indexed View

---

An entirely new solution based on Indexed Views is possible.

An Indexed View is a view which has a clustered index on it, and the data is actually stored on disk.

As I understand it, you are trying to keep a sum of purchases per product item stored in tblProduct. I have assumed that ItemCode is the PK of tblProduct and that ItemName is also defined there (We cannot use MAX in an indexed view). So we can define a view like this:

CREATE VIEW dbo.vwTotalPurchases
WITH SCHEMABINDING  -- must be schema bound, we cannot change underlying columns after creation
AS
SELECT
   ItemCode,
   SUM(Quantity) QuantityPurchased,
   COUNT_BIG(*) CountPurchases  -- if we group, must have count also, so that rows can be maintained
FROM dbo.tblPurchase  -- must use two-part names
GROUP BY itemCode;
GO

We can then create a clustered index on it to persist it on disk. SQL Server will maintain the index whenever an update to the base table happens. If there are no more rows in the grouping (identified by count being 0), then the row is deleted:

CREATE UNIQUE CLUSTERED INDEX PK_vwTotalPurchases ON dbo.vwTotalPurchases (ItemCode);
GO

Now if we want to query it, we can left join this view onto tblProducts (left join because there may be no purchases):

SELECT
    p.ItemCode,
    p.ItemName,
    ISNULL(tp.QuantityPurchased, 0) QuantityPurchased,
    ISNULL(tp.CountPurchases, 0) CountPurchases
FROM tblProducts p
LEFT JOIN vwTotalPurchases tp WITH (NOEXPAND) ON tp.ItemCode = p.ItemCode;

We can define this as a view also (not an indexed one, but a standard view) so that the definition is usable anywhere.

---

Note on NOEXPAND:

If you are not on SQL Server Enterprise or Developer Edition, you must use the hint WITH (NOEXPAND) to force it to use the index, otherwise it will query the base tblPurchase instead. And even in those editions, it is best to use NOEXPAND.

See this article by Paul White on this.