Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
By filtering data from actual table i am inserting it into temporary table then some bulk values are coming from it then again i am inserting it into another temporary table for check loop wise and update some values in actual table into database, so how to retrieve column values looping wise? till here i reached

CREATE TABLE #result 
        (           
           PackagePeriod     varchar(20),
           UserId            INT,           
           OldExpiryDate     DATE,         
           AmountToPay       FLOAT,
           PyingAmount       FLOAT,
           Balance           FLOAT,
           LastPaidDate      DATE,
           Company_Id        INT           
        )

create table #temp
        (
            PackagePeriod    varchar(20),
           UserId            INT,           
           OldExpiryDate     DATE,         
           AmountToPay       FLOAT,
           PyingAmount       FLOAT,
           Balance           FLOAT,
           LastPaidDate      DATE,
           Company_Id        INT
        )           

        SET @cmd ='.... where OldExpiryDate<=Replace(CONVERT(VARCHAR(20), (GetDate()-1), 106),' ','-')

    select @count=(select COUNT(PackagePeriod) from #temp)

    if(PackagePeriod='Monthly')
    begin
    update ...
    end
    else if(PackagePeriod='Quarterly')
    begin
    update ..
Posted
Updated 1-Jul-15 2:31am
v2

--- From whatever i can understand from your question above


DECLARE @count INT
DECLARE @I INT
DECLARE @PackageVar NVARCHAR(100)
DECLARE @Loop INT

SET @Loop = 100

SELECT @I = 1
SELECT @count = COUNT(PackagePeriod) FROM #temp

WHILE (@I <= @count )
BEGIN

SELECT @PackageVar = PackagePeriod
FROM #temp WHERE Company_Id = @Loop --Assuming Primary ID(Company_Id ) from 100

If(@PackageVar == 'Monthly')
UPDATE
Else IF(@PackageVar == 'Quarterly')
UPDATE
SELECT @I = @I + 1
SELECT @Loop = @Loop + 1
END
 
Share this answer
 
Comments
dawood abbas 2-Jul-15 8:30am    
i dnt have any condition to retrieve data from column in loop..
CREATE TABLE #result 
        (           
           PackagePeriod     varchar(20),
           UserId            INT,           
           OldExpiryDate     DATE,         
           AmountToPay       FLOAT,
           PyingAmount       FLOAT,
           Balance           FLOAT,
           LastPaidDate      DATE,
           Company_Id        INT           
        )

declare @PackagePeriod     varchar(20)
declare @UserId            int          
declare @OldExpiryDate     DATE         
declare @AmountToPay       FLOAT
declare @PyingAmount       FLOAT
declare @Balance           FLOAT
declare @LastPaidDate      DATE
declare @Company_Id        INT           

declare result_cursor cursor for 
        SELECT PackagePeriod, UserId,  OldExpiryDate,  AmountToPay, PyingAmount, Balance, LastPaidDate, Company_Id from  #result

OPEN result_cursor

FETCH NEXT FROM result_cursor INTO @PackagePeriod, @UserId,  @OldExpiryDate,  @AmountToPay, @PyingAmount, @Balance, @LastPaidDate, @Company_Id

WHILE @@FETCH_STATUS = 0

BEGIN

    if(@PackagePeriod='Monthly')
    begin
       -- update your table 
    end
    else if(@PackagePeriod='Quarterly')
    begin
        --update table
    end

  FETCH NEXT FROM result_cursor INTO @PackagePeriod, @UserId,  @OldExpiryDate,  @AmountToPay, @PyingAmount, @Balance, @LastPaidDate, @Company_Id

END

CLOSE result_cursor

DEALLOCATE result_cursor
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900