CODEWITHSUNDEEP
sqlsql-servercursor
Nejthe
Nejthe

Reputation: 580

Adding values to an existing table by using Cursor

By running this code its working, but when I add more values to the table it's not working anymore. I appreciate any help, thank you.

This code works perfectly:

declare @id int
declare @empid int
set @id = 0
declare @schedindate datetime
declare @ss nvarchar(100)
declare @indice nvarchar(2)
declare @FromDate datetime
declare @ToDate datetime
declare @TimeInR datetime
declare @TimeOutR datetime
declare @departmentID int 
declare @PositionID int
declare @BranchID int
declare @SupervisorID int
declare @GradeID int
declare @Custom1ID int
declare @Custom2ID int
declare @PayClassID int
declare @EmploymentType int
set @FromDate = '2009-01-14'
set @ToDate = '2010-01-30'
delete from table1
declare cc cursor for select distinct empid from ta_timecard where schedindate between @FromDate and @ToDate
open cc
fetch next from cc into @empid
while (@@fetch_status = 0)
begin
    set @id = @id + 1
    insert into table1 (ID, EmpID) values (@id, @empid)

    declare cc2 cursor for select distinct departmentid from ta_timecard where empid = @empid and schedindate between @FromDate and @ToDate
    open cc2
    fetch next from cc2 into @departmentID

    while (@@fetch_status = 0)
    begin
        set @indice = cast(datediff(day, @fromdate, @schedindate) as nvarchar(4))
        set @ss = 'update table1 set departmetid = ' + convert(nvarchar(4), @departmentID)
            + ' where empid = ' + convert(nvarchar(4), @empid)

        execute sp_executesql @ss
        fetch next from cc2 into @departmentID
    end
    close cc2
    deallocate cc2
fetch next from cc into @empid
end
close cc
Deallocate cc
GO

enter image description here

But when I add more values to the table I got the first row only affected

enter image description here

declare @id int
declare @empid int
set @id = 0
declare @schedindate datetime
declare @ss nvarchar(100)
declare @indice nvarchar(2)
declare @FromDate datetime
declare @ToDate datetime
declare @TimeInR datetime
declare @TimeOutR datetime
declare @departmentID int 
declare @PositionID int
declare @BranchID int
declare @SupervisorID int
declare @GradeID int
declare @Custom1ID int
declare @Custom2ID int
declare @PayClassID int
declare @EmploymentType int
set @FromDate = '2009-01-14'
set @ToDate = '2010-01-30'
delete from table1
declare cc cursor for select distinct empid from ta_timecard where schedindate between @FromDate and @ToDate
open cc
fetch next from cc into @empid
while (@@fetch_status = 0)
begin
    set @id = @id + 1
    insert into table1 (ID, EmpID) values (@id, @empid)

    declare cc2 cursor for select distinct departmentid, branchid from ta_timecard where empid = @empid --and schedindate between @FromDate and @ToDate
    open cc2
    fetch next from cc2 into @departmentID, @BranchID--,@PositionID

    while (@@fetch_status = 0)
    begin
        set @indice = cast(datediff(day, @fromdate, @schedindate) as nvarchar(4))
        set @ss = 'update table1 set departmetid = ' + convert(nvarchar(4), @departmentID) 
            +', branchid = ' + convert(nvarchar(4), @BranchID) 
            --+ ', positionid = ' + convert(nvarchar(4), @PositionID)
            + ' where empid = ' + convert(nvarchar(4), @empid)
print(@ss)
        execute sp_executesql @ss
        fetch next from cc2 into @departmentID, @BranchID--, @PositionID
    end
    close cc2
    deallocate cc2
fetch next from cc into @empid
end
close cc
Deallocate cc
GO

Edited: this is the table ta_TimeCard

enter image description here

Upvotes: 3

Views: 1786

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

you need check @BranchID on NULL values

+ ISNULL(', branchid = ' + convert(nvarchar(4), @BranchID), '')

Upvotes: 1

Tan Suiwseng
Tan Suiwseng

Reputation: 77

Please change your query from

  set @ss = 'update table1 set departmetid = '

to

set @ss = 'update table1 set departmentid = '

the n might affect your result

Upvotes: 0

Related Questions