[RESOLVED] double cursors usage

[sapator]

Hi.
I'm trying to use a cursor inside a cursor.
As an initial attempt it seemed to work with just to cursor inside each other but then I used it on an insert and it hanged out.
I believe there is some issues with the @@FETCH_STATUS value used on both
As a result I have modified the cursors and I wanted to ask if the below is correct.

Thanks.

  DECLARE MYC CURSOR FAST_FORWARD READ_ONLY FOR 
     SELECT Cinema_strCode, Cinema_strServerName, Cinema_strDatabaseName, Cinema_strUserId, Cinema_strPWD
    FROM tblCinema
   	where Cinema_strCode in (01,11)
	ORDER BY Cinema_strCode

    OPEN MYC
    FETCH NEXT FROM MYC INTO @CinemaCode, @CinemaServerName, @CinemaDatabaseName, @CinemaUserID, @CinemaPwd

    SET @Outer_loop = @@FETCH_STATUS
    WHILE @Outer_loop = 0

  
    BEGIN
      

        EXECUTE @RC = spSYSConnectToServer @CinemaServerName, @CinemaDatabaseName, @CinemaUserID, @CinemaPwd
        IF @RC <> 0    
        BEGIN
		-- GOTO ERROR_POINTMYC
		print 'error'
		return
		End
		else
		begin
		print 'Checking'
		end		


	INSERT INTO @LocationTVP ( trans )
      SELECT  transaction_POStransactionId
      FROM    #tempL 
	  Where transaction_membershipid = @membershipid
	  AND CinemaID = @CinemaCode


	 
		----* IF 1
	  if (Select count(trans) from @LocationTVP) > 0 
	  BEGIN
	  print 'tr'



/*Loop second cursor */

    DECLARE MYCIN CURSOR FAST_FORWARD READ_ONLY FOR 
    SELECT trans
    FROM @LocationTVP
    

    OPEN MYCIN
    FETCH NEXT FROM MYCIN INTO @LocationTVPI


     Set @inner_loop = @@FETCH_STATUS
    WHILE @Inner_loop = 0

  --  WHILE @@FETCH_STATUS = 0 
    BEGIN

    set @SqlString = N'exec DWCINEMASRV.Vrlob.dbo.sojrs @param1'
    set @ParamDef = N'@param1 int'

	
	print @LocationTVPI

	EXECUTE sp_executesql @SqlString ,@ParamDef, @param1 = @LocationTVPI
	

	     FETCH NEXT FROM MYCIN INTO @LocationTVPI
		 Set @inner_loop = @@FETCH_STATUS
    END

    CLOSE MYCIN
    DEALLOCATE MYCIN

	


	    IF @RCREMOTE <> 0   
		BEGIN
		--GOTO ERROR_POINTMYC
		print 'error'
		return
		END	 
	  ELSE
	  BEGIN
	  print 'ok'
	  END

	  --* IF 1  -- IF PER CURSOR ENDS HERE
	  END

	
	  -- Clear table
	  delete from @LocationTVP
	  
        FETCH NEXT FROM MYC INTO @CinemaCode, @CinemaServerName, @CinemaDatabaseName, @CinemaUserID, @CinemaPwd
		 SET @Outer_loop = @@FETCH_STATUS
    END

    CLOSE MYC
    DEALLOCATE MYC

[szlamany]

Can you put the second cursor into a stored procedure of it's own??

[techgnome]

Personally I'm not a fan of cursors in the first place. I'd dump the results of the select from the first cursor into a table var, which would include either an IDENTITY field, or use the ROW_NUMBER() function to get a sequential number. Then I'd use a loop to get the rows out one by one until there's no more to process. For the second cursor. I'd probably do something similar... but I can't see how exactly the data was put in ... where did #tempL come from?

Regardless... I'm not a fan of cursors, so I loop to other means of looping through the data when I can. I haven't run in to a case where a cursor was the only way.

-tg

[GaryMazzone]

I've used cursors inside a cursor loop before with no issues.... Not the best code of the fastest but it got the one time job done in reasonable time

[sapator]

Hi.
This wouldn't have been an issue as I would have passed it to a Table-Valued Parameters. Unfortunately whatever i tried just errored out saying that Linked server does not support Table-Valued Parameters.
It supposedly would work with a sp_executesql . Supposedly as it still said the same dam thing. So I used a cursor to iterate the transactions of the rows one by one.

I script out #tempL . The main issue is to pass the @LocationTVP to a Linked server. The server said what I wrote above. Yes I could probably do what techgnome suggested but I was in a cursor mood. as I wrote the external already and I had to proceed fast on this solution.

But is the cursor OK as I've wrote it? Can you see any issues?

[szlamany]

Personally I'm not a fan of cursors in the first place. I'd dump the results of the select from the first cursor into a table var, which would include either an IDENTITY field, or use the ROW_NUMBER() function to get a sequential number. Then I'd use a loop to get the rows out one by one until there's no more to process. For the second cursor. I'd probably do something similar... but I can't see how exactly the data was put in ... where did #tempL come from?

Regardless... I'm not a fan of cursors, so I loop to other means of looping through the data when I can. I haven't run in to a case where a cursor was the only way.

-tg

I also prefer to use a TABLE VAR with Row_Number() or some other natural key for looping.

The only place I ever ran into a problem with this and NEEDED TO USE A CURSOR, was when the number of rows was large. CURSOR's will scale very large. TABLE VAR's do not scale well.

[FunkyDexter]

But is the cursor OK as I've wrote it? Can you see any issues?

I've cast an eye over it in Management Studio and it looks OK but it's not easy to tell without running it.

You're checking Fetch_Status correctly so it should exit both the inner and the outer cursors appropriately. If it hangs I think it will be because of something else rather than the cursor itself.

[techgnome]

Hi.
This wouldn't have been an issue as I would have passed it to a Table-Valued Parameters. Unfortunately whatever i tried just errored out saying that Linked server does not support Table-Valued Parameters.
It supposedly would work with a sp_executesql . Supposedly as it still said the same dam thing. So I used a cursor to iterate the transactions of the rows one by one.

I script out #tempL . The main issue is to pass the @LocationTVP to a Linked server. The server said what I wrote above. Yes I could probably do what techgnome suggested but I was in a cursor mood. as I wrote the external already and I had to proceed fast on this solution.

But is the cursor OK as I've wrote it? Can you see any issues?

I never said anythign about passing the TV to an sproc, let alone one on a linked server... I said to use a TV and loop through it INSTEAD of using a cursor the way you are...

The reason you can't pass a TV as a parameter to a linked server is because it has no way of knowing if the remote server knows anything about the TV definition. It wouldn't do any good to pass an Orange to the linked server if that server then looks at it and goes "what the heck is this?" And no, I don't think it would do any good to define it in both locations. ... each would be in a different schema... it's like defineing the same class in two namespaces... their Fully Qualified Name ends up being different, and as . result, they are two completely distinctly different things.

-tg

[sapator]

Thanks all.
Techgnome. You misunderstood. I said that I wanted to pass a TV to a sproc in linked server but I get denied. And yes I have defined it in both locations and is as you said. It does not play along.
If I would use a loop I would do multiple execs in linked server with a simple int value. I can't see any other way if I can't pass the TV. Would be nice tho if we can create an exact same TV in both links and it would work. You are defining an exact same type in both servers. I would expect it to work but it doesn't.

[techgnome]

Actually... you can... but you need to think laterally... Select the data you want, add a FOR XML clause to it and shape the results into an XML string... pass the XML string to your sproc on the linked server, which can then shred the XML back into it's table format and into a table var on that end and use the data...

-tg

[sapator]

Hi.
Yes yes, I've read this, forgot to mention it. Found it a bit more difficult to do so as then I would have to debug XML'D data for sensitive information.

[sapator]

Found some time so I'm trying to implement a loop here.
Does the below looks OK? (it's not the initial SQL code by I will convert it)

--inside cinema loop

DECLARE @table1 TABLE (trans int )


declare @trans int

insert into @table1
SELECT trans
FROM @LocationTVP




while exists (select * from @table1)
begin

  set @trans=(  select top 1 trans = trans
    from @table1
    order by trans asc)

	exec MYsproc @trans

    -- remove from loop
	Delete @table1 Where trans = @trans
 

end

Thanks.

[techgnome]

It's not quite the way I would have done it... but if you understand how it works, and it works for you... that's what matters.

If you're interested, this is how I would have gone about it:

declare @tmpTable table (rowNum IDENTITY(1,1) not null, trans int)
Insert into @tmpTable (trans) 
select trans from @LoationTVP

Declare @RowNum int = 1
Declare @Trans int

while exists (select * from @tmpTable where RowNum = @owNum)
begin 
 select @Trans = trans from @tmpTable where RowNum = @RowNum
 exec MySproc @Trans
 Set @RowNum = @RowNum + 1
end

-tg

[szlamany]

I always use this technique. I like to use IS / IS NOT NULL for it's natural purpose...

	Set @DocId=(Select Min(DocId) From @BD)
	While @DocId is not null
	Begin
		Select @DocName=DocName
				,@StoredFileName=StoredFileName
				,@FormDescr=FormDescr
				From @BD --AWCDocuments_T
				Where DocId=@DocId
		.
		.
		.
		Set @DocId=(Select Min(DocId) From @BD Where DocId>@DocId)
	End

[sapator]

Thanks for the examples.
I completely understand techgnome's example but I'm a little skeptic about szlamany. My thought is on the "min" from what I understand is that docid will eventually NULL out when there is no small id.
It's not your fault and probably forgot to mention that we may end up with rows that my have "id=1 , cinema =2 and id=1 cinema=3" so the same id multiple times. So @docID would probably have to be some unique id (such as
IDENTITY ) if so then we will end up on something close to techgnome's example?
Thanks.

[szlamany]

@sapator - if you did not have a naturally occurring numeric key, then a Row_Number() would do it for you...

[sapator]

Hi.
I'm checking my queries and I've been using rank() OVER instead. Is there any difference?
Thanks.

[FunkyDexter]

This thread gives a pretty good description of the differences. You'll only see a difference if more than one row would appear in the same place according to the Order By - e.g. two rows have the same Cinema where you're ordering by Cinema. If you're ordering by a unique key they'll return the same result.

[sapator]

Hi.
I will be ordering by cinema and transaction id " select rank() OVER (ORDER BY cinemaid,transactionid ).....
You cannot have a cinema with the same transactionid but you can have 2 cinemas that transactions may be the same.
Does rank qualify here?
thanks

[szlamany]

imo, RowNumber is designed to serve out unique row numbers based on ORDER BY

Rank() has a specific purpose - as in assigning CLASS RANK for students in higher ed. That CLASS RANK will be "same value" for those with SAME SORT values - so you could get 1, 2, 3, 3, 3, 6, 7, 8.

Since Rank() has this feature it should only be used when you need to have this functionality, imo.

[sapator]

Just reviewed the queries I've ranked. I can't quite understand what I was writing back then but it seems that we needed some commonly used vouchers ranked together.
So I guess I did that back then. I think rank will also be working here but as per your answers I should be better of using Row_Number() .
Thanks.