T-SQL Cursor problem - 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

General SQL Server Forums

New to SQL Server Programming

T-SQL Cursor problem

Author

Topic

hahsm
Starting Member

10 Posts

Posted - 2009-02-10 : 22:33:23

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 22:36:22

you're just putting values one by one in two tables without any relation. is this what you really want? also why using cursor? cant you use set based technique?

hahsm
Starting Member

10 Posts

Posted - 2009-02-11 : 01:19:43

There is no relationship between the two temporary tables. I just want to retrieve data from the two cursors (Since cursors work like an array) into the variables. However, its duplicating like I have demonstrated an example output. Could you please elaborate more on the set-based technique or what I am doing wrong here. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 03:12:19

then how will you dtermine which value from 1 table should be mingled with which value of otehr? or do you just need a cartesian product?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 04:01:30

@@FETCH_STATUS is only relevant to latest cursor operation.

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

declare @err int

Fetch Next from InCard into @InCard
set @err = @@Fetch_Status 

Fetch Next from OutCard into @OutCard
set @err = @err + @@Fetch_Status 

While @err = 0
	begin
		Insert into #myTable (InCard, OutCard)
		Values (@InCard, @OutCard)

		Fetch Next from InCard into @InCard
		set @err = @err + @@Fetch_Status 

		Fetch Next from OutCard into @OutCard
		set @err = @err + @@Fetch_Status 
	End


close InCard
close OutCard

deallocate InCard
deallocate OutCard

E 12°55'05.63"
N 56°04'39.26"

hahsm
Starting Member

10 Posts

Posted - 2009-02-11 : 08:10:58

Thanks for the reply guys! it worked!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 08:18:45

Thank you.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources