Nested Cursors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Nested Cursors

Author

Topic

hahsm
Starting Member

10 Posts

Posted - 2009-02-10 : 10:23:36

I have two tables containing filtered records from two tables respectively. I am inserting values from the tables into two separate cursors, CursorA, and CursorB. Then I am trying to insert values from the two cursor set variables simultaneously into a single table. (for example, IN CARD, and Respective OUT CARD). When I did this, it is inserting the same record for the second column. suppose, column A: contains two distinct records from CursorA, but column B contains duplicates, since CursorB contains only 1 recordset. How can I manipulate this scenario, even without using CURSORS. I have tried even WHILE LOOP

Any help would be appreciated. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 10:30:16

post some sample data to illustrate the problem. we cant make much from explanation

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

hahsm
Starting Member

10 Posts

Posted - 2009-02-10 : 11:06:04

It seems like a huge SQL Query. However, here is the query,

Create table #tmpDailyCard (Unique_Key int IDENTITY(1,1) PRIMARY KEY, InOutCard datetime)

Insert into #tmpDailyCard SELECT
top 100 percent checktime
FROM
[Att2007].[dbo].[CHECKINOUT]
Where UserID = @UserID
and convert(char(8),checktime,8) > convert(char(8),@OutTimeCard ,8)
and convert(char(10), checktime, 112) = convert(char(10), @CurrDate, 112)

DECLARE TimeSlotSearchForInCard CURSOR FAST_FORWARD LOCAL FOR
SELECT s1.Unique_Key, s1.InOutCard
FROM
#tmpDailyCard s1 JOIN #tmpDailyCard s2
ON (s1.Unique_Key >= s2.Unique_Key)
Group By s1.Unique_Key, s1.InOutCard
Having (COUNT(*) % 2) = 1 --Gets the ODD Data

OPEN TimeSlotSearchForInCard

DECLARE TimeSlotSearchForOutCard CURSOR FAST_FORWARD LOCAL FOR
SELECT s1.Unique_Key, s1.InOutCard
FROM
#tmpDailyCard s1 JOIN #tmpDailyCard s2
ON (s1.Unique_Key >= s2.Unique_Key)
Group By s1.Unique_Key, s1.InOutCard
Having (COUNT(*) % 2) = 0 --Gets the EVEN Data

OPEN TimeSlotSearchForOutCard

FETCH NEXT FROM TimeSlotSearchForOutCard INTO @PrK, @OutCard
FETCH NEXT FROM TimeSlotSearchForInCard INTO @PrK, @InCard
print @InCard
print @OutCard
WHILE @@FETCH_STATUS <> -1 -- BEGIN LOOPING FOR SELECTED TIME SLOTS
BEGIN
IF NOT @OutCard < 0 AND NOT EXISTS (Select * from overtime Where convert(char(10), Date, 112) = convert(char(10),@CurrDate,112)
and StaffID = @StaffID
and convert(char(8),OTStartTime,8) = convert(char(8),@InCard,8))
BEGIN
Print 'The out card is not null'
If @Incard > @OutCard
BEGIN
IF @DOW = 1 or @DOW = 7 or @OffHoliday < 0
Insert into Overtime (Date, StaffID, OTStartTime, OTEndTime, WeekEndsOT, WeekEndsOT_Status)
values (@CurrDate, @StaffID, @InCard, Null, 0.00, -1)
ELSE
Insert into Overtime (Date, StaffID, OTStartTime, OTEndTime, WeekDaysOT, WeekDaysOT_Status)
values (@CurrDate, @StaffID, @InCard, Null, 0.00, -1)
END
ELSE
BEGIN
IF NOT EXISTS (Select * from overtime Where convert(char(10), Date, 112) = convert(char(10),@CurrDate,112)
and StaffID = @StaffID
and convert(char(8),OTStartTime,8) = convert(char(8),@InCard,8))
BEGIN
IF @DOW = 1 or @DOW = 7 or @OffHoliday < 0
INSERT INTO Overtime
(
Date, StaffID, OTStartTime, OTEndTime, WeekEndsOT, WeekEndsOT_Status
)
VALUES
(
@CurrDate, @StaffID, @InCard, @OutCard, cast(datepart(Hour, convert(char(8),@OutCard-@InCard,8)) as nvarchar)
+ '.' +
CASE
WHEN cast(datepart(Minute, convert(char(8),@OutCard-@InCard,8)) as nvarchar) < 10
THEN '0' + cast(datepart(Minute, convert(char(8),@OutCard-@InCard,8)) as nvarchar)
ELSE
cast(datepart(Minute, convert(char(8),@OutCard-@InCard,8)) as nvarchar)
END, -1
)
ELSE
INSERT INTO Overtime
(
Date, StaffID, OTStartTime, OTEndTime, WeekDaysOT, WeekDaysOT_Status
)
VALUES
(
@CurrDate, @StaffID, @InCard, @OutCard, cast(datepart(Hour, convert(char(8),@OutCard-@InCard,8)) as nvarchar)
+ '.' +
CASE
WHEN cast(datepart(Minute, convert(char(8),@OutCard-@InCard,8)) as nvarchar) < 10
THEN '0' + cast(datepart(Minute, convert(char(8),@OutCard-@InCard,8)) as nvarchar)
ELSE
cast(datepart(Minute, convert(char(8),@OutCard-@InCard,8)) as nvarchar)
END, -1
)
END
END
END

FETCH NEXT FROM TimeSlotSearchForOutCard INTO @PrK, @OutCard
FETCH NEXT FROM TimeSlotSearchForInCard INTO @PrK, @InCard

END

--Select * From #tmpTimeSlots
--Select sum(cast((overtime) as numeric(18,2))) From #tmpTimeSlots

-------------------------------------------------------------------------------
IF Cursor_Status('local', 'TimeSlotSearchForInCard')<=0 --<=0
DEALLOCATE TimeSlotSearchForInCard
ELSE
BEGIN
IF Cursor_Status('local', 'TimeSlotSearchForInCard')>0 -->0
BEGIN
Print 'Closing and deallocating cursor'
CLOSE TimeSlotSearchForInCard
DEALLOCATE TimeSlotSearchForInCard
END
END

IF Cursor_Status('local', 'TimeSlotSearchForOutCard')<=0
--Print 'Deallocating cursor'
DEALLOCATE TimeSlotSearchForOutCard
ELSE
BEGIN
IF Cursor_Status('local', 'TimeSlotSearchForOutCard')>0
BEGIN
Print 'Closing and deallocating cursor'
CLOSE TimeSlotSearchForOutCard
DEALLOCATE TimeSlotSearchForOutCard
END
END

-------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#tmpTimeSlots') IS NOT NULL
BEGIN
DELETE FROM #tmpTimeSlots
DROP TABLE #tmpTimeSlots
END

IF OBJECT_ID('tempdb..#tmpDailyCard') IS NOT NULL
BEGIN
DELETE FROM #tmpDailyCard
DROP TABLE #tmpDailyCard
END

Please let me know the point I am doing wrong.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 11:15:17

did you read link at all? post the info in specified format

hahsm
Starting Member

10 Posts

Posted - 2009-02-10 : 11:36:20

Sorry for the BAD CODE.

Here is an actual pseudo code of what I am trying to accomplish.

Declare Incard Cursor FOR Select InCard From #TempInCardTable Where InCard = (--Some Condition)
Declare OutCard Cursor FOR Select OutCard From #TempOutCardTable Where OutCard = (--Some Condition)

Open InCard
Open OutCard

Declare @InCard DateTime
Declare @OutCard DateTime

Fetch Next from InCard into @InCard
Fetch Next from OutCard into @OutCard
While @@Fetch_Status <> -1
Insert into #myTable (InCard, OutCard)
Values (@InCard, @OutCard)
Fetch Next from InCard into @InCard
Fetch Next from OutCard into @OutCard
End

How ever, if InCard cursor contains 2 recordsets, and OutCard cursor contains 1 recordsets, it is duplicating the values while inserting the OutCard value into #myTable.

The result was:
InCard OutCard
1/2/2009 10:35:04 11:00:03
1/2/2009 11:40:54 11:00:03

Hope I have explained everything. Since my code is huge, its really difficult to post as you required. Sorry for that. Hope u understood my problem.

Thanks for the reply!

hahsm
Starting Member

10 Posts

Posted - 2009-02-10 : 12:08:52

I would really appreciate any help for the above problem, since I have been struggle with that code for 2 days to sort it out perfectly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources