|
|
by
Dhaval Patel
on
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
|
|