Post
Ask Question

How to Get sum of first record amount and second amount is in Total Amount using Sql server STored Procedure

Get sum of first record amount and second amount is in  Total Amount
In this table i want to insert value fot sum of first and second temp_id is total_sum of second temp_id and than after repeat loop for it.
This is Table Script.

 USE [temp]
GO
/****** Object:  Table [dbo].[TblTest]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TblTest](
	[temp_id] [int] IDENTITY(1,1) NOT NULL,
	[temp_name] [varchar](50) NULL,
	[sum] [int] NULL,
	[Total_sum] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Stored Procedure :-

Alter procedure ProcTestGet
@temp_name varchar(50),
@sum int
AS
 Declare @Tmp_id As int
 Declare @total_sum As int 
Declare @sum1 As int
select @Tmp_id = @@identity
If @Tmp_id = 0          
Begin        
		set @total_sum =Null  
		insert into TblTest(temp_name,sum,total_sum) VALUES(@temp_name,@sum,@total_sum)
End        
else
begin
		select @total_sum = total_sum  from TblTest where temp_id = @@identity
		if @total_sum  = NULL
		begin
			select @sum1= sum from TblTest where temp_id = @@identity
			declare @totalsum int
			set @totalsum =  @sum1 + @sum
			insert into TblTest(temp_name,sum,total_sum) VALUES(@temp_name,@sum,@totalsum)
		end
		else
		begin
			select @total_sum = total_sum  from TblTest where temp_id = @@identity
			declare @totalsum1 int
			set @totalsum1 = @total_sum + @sum
			insert into TblTest(temp_name,sum,total_sum) VALUES(@temp_name,@sum,@totalsum1)
		end
select @@identity
End

 exec ProcTestGet  '1',600    -- Execeute Stored Procedure
 select * from TblTest           -- Run query to see result
Next Recommended Reading Calling a Stored Procedure/Function in Entity Framework using DBContext