SIGN UP MEMBER LOGIN:    
Blog

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

Posted by Dhaval Patel Blogs | .NET 4.5 Jan 18, 2012
Stored procedure using first sum and second sum total is goes to second id total_sum value.
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
share this blog :
post comment