如何将SQL Server存储过程计算结果插入dbo.Store表Inventory列？

Hey there! Let's get your stored procedure working the way you need it to—right now it only selects the calculated Inventory value, but doesn't actually write that value into the dbo.Store table's Inventory column. Here's how to adjust it based on your actual goal:

Option 1: Update Existing Rows (Most Likely What You Need) #

If you want to set the Inventory column for every existing row in dbo.Store to the difference between Stock and Spoilage, use this updated procedure:

ALTER PROCEDURE [dbo].[sp_StoreBilling]
AS
BEGIN
    SET NOCOUNT ON;

    -- Update the Inventory column directly with the calculated difference
    UPDATE dbo.Store
    SET Inventory = Stock - Spoilage;

    -- Optional: Return the updated rows to verify changes
    SELECT Stock, Spoilage, Inventory
    FROM dbo.Store;
END

Key Notes: #

• I renamed the procedure from sp.StoreBilling to sp_StoreBilling—using dots in object names can cause confusion in SQL Server (since dots are used to separate schemas and objects).
• The original @Stock and @Spoilage variables were unnecessary; we can calculate the difference directly in the UPDATE statement for each row.
• The SET NOCOUNT ON; line prevents extra "rows affected" messages from being returned, which is standard for stored procedures.

Option 2: Insert New Rows (If You Need to Add New Records) #

If your goal is to insert new rows into dbo.Store using the calculated Inventory value (instead of updating existing ones), use this version. Note: This will duplicate existing rows with the calculated Inventory—add a WHERE clause if you only want to insert specific records:

ALTER PROCEDURE [dbo].[sp_StoreBilling]
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert new rows with the calculated Inventory value
    INSERT INTO dbo.Store (Stock, Spoilage, Inventory)
    SELECT Stock, Spoilage, Stock - Spoilage
    FROM dbo.Store;

    -- Optional: Return the newly inserted rows
    SELECT * FROM dbo.Store WHERE Inventory = Stock - Spoilage;
END

Pick the option that matches your business logic, test it out, and you're good to go!

内容的提问来源于stack exchange，提问作者wire_jp