Click here to Skip to main content
15,892,737 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,

I created a stored procedure in ms sql server. The stored procedure subtracts two columns in the same table. How do I insert this stored procedure data into the Inventory column of an existing table dbo.Store? The stored procedure is:

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

DECLARE @Stock decimal(10,2)=0.00
DECLARE @Spoilage decimal(10,2)=0.00

SELECT A.Stock, A.Spoilage, A.Stock-A.Spoilage AS Inventory
FROM dbo.Store AS A
END


What I have tried:

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

DECLARE @Stock decimal(10,2)=0.00
DECLARE @Spoilage decimal(10,2)=0.00

SELECT A.Stock, A.Spoilage, A.Stock-A.Spoilage AS Inventory
FROM dbo.Store AS A
INSERT INTO  dbo.Store
OUTPUT Inventory
VALUES(@Stock-@Spoilage); 
END


Error message: 
Msg 213, Level 16, State 1, Procedure sp_StoreBilling, Line 20 [Batch Start Line 7]
Column name or number of supplied values does not match table definition.
Posted
Updated 20-Oct-19 8:55am
v3
Comments
wire_jp 19-Oct-19 9:41am    
@Tadit Dash: I want to insert into the column called Inventory in the dbo.Store table.

I was able to solve my problem by creating a computed column in the existing table instead of using a stored procedure. I open the table in design view and I modified the Inventory column by going to its properties and selecting Computed Column > Formula. I added the Formula:

Stock - Spoilage

I saved the formula.
 
Share this answer
 
v2
You have not provided the column name for the insert statement.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900