CELKO (9/18/2012)

---

I have a product_sku CHAR(10) s table and a storage product_sku CHAR(10) s table.

No, you do not. A table has a key and you have none. What you have are two decks of punch cards written in SQL. You have no DRI or other constraints. And there is a serious design flaw called non-normal form redundancy (Google it).First let's clean that mess:

CREATE TABLE Products

(product_sku CHAR(10) NOT NULL PRIMARY KEY,

product_description VARCHAR(50) NOT NULL);

We would never store a total we can compute in the products table. You would have to fix this design flaw with triggers! We hate triggers and procedural code in SQL.

CREATE TABLE Warehouses

(product_sku CHAR(10) NOT NULL

REFERENCES Products(product_sku)

ON DELETE CASCADE

ON DELETE UPDATE,

warehouse_nbr INTEGER NOT NULL,

PRIMARY KEY (product_sku, warehouse_nbr),

stock_cnt INTEGER NOT NULL

CHECK (stock_cnt >= 0));

Now let's look at your procedures. You have not heard of the MERGE statement? It is also known as an UPSERT in some SQL products.

CREATE PROCEDURE Upsert_Product

(@in_warehouse_nbr INTEGER,

@in_product_sku CHAR(10),

@in_stock_cnt INTEGER)

AS

BEGIN

SET NOCOUNT ON;

MERGE INTO Warehouse AS Target

USING (SELECT X.*

FROM (VALUES (@in_warehouse_nbr, @in_product_sku, @in_stock_cnt))

AS Source (warehouse_nbr, product_sku, stock_cnt))

ON Target.warehouse_nbr = @in_warehouse_nbr

AND Target.product_sku = @in_product_sku

WHEN MATCHED

THEN UPDATE

SET stock_cnt = stock_cnt + @in_stock_cnt

WHEN NOT MATCHED

THEN INSERT

VALUES (@in_warehouse_nbr, @in_product_sku, @in_stock_cnt);

END;

Your second procedure is not needed at all. The trigger is also a waste of code and time. SQL programmers put this kind of computation in a VIEW where it is always current and invoked only when needed.

CREATE VIEW Stock_Levels (product_sku, product_description, stock_cnt_tot)

AS

SELECT P.product_sku, P.product_description, SUM(stock_cnt)

FROM Products AS P

LEFT OUTER JOIN

Warehouses AS W

ON P.product_sku = W.product_sku

GROUP BY P.product_sku, P.product_description;