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
Lakshmi Reddy YeluriPosted Aug 28, 2012, 2:53 AM
Not calculating/ inserting @total_sum value into the records while adding...I think @@Identity giving results always 0 only.. Can you please update me the same. Thanks In Advance..