Click here to Skip to main content
14,361,340 members
Rate this:
Please Sign up or sign in to vote.
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 9: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.
Rate this:
Please Sign up or sign in to vote.

Solution 2

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.
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

You have not provided the column name for the insert statement.

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100