CURSOR QUESTION

[icemel]

Hi,

I have a question about cursors.

A "Forward_Only" cursor reflects changes to the underlying data, and does not use tempdb.

A "Static" cursor does *not* reflect changes to the underlying data and *does* use tempdb.

So, my question is, if you declare a cursor as:
CURSOR LOCAL FORWARD_ONLY STATIC

aren't you issuing 2 contradictory statements, as it were?
How does this work if one type of cursor is "static" and the other "dynamic" (in the sense that the forward_only cursor reflect changes to the underlying data)

There is some question about this among my colleagues here at work.

Thanks much

 

[icemel]

well, i answered my own question

forward_only with static makes it static

so my question now is, is there any performance benefit to making a cursor forward_only (provided scrolling is not needed)

thanks!

 

[SQLSister]

What you should be asking is why use a cursor at all. Cursors are the one of the worst things you can do to a database to affect performance negatively. They are almost always unnecsessary for selects, updates, inserts, and deletes to ordinary tables of the database.

What are you trying to do with a cursor?

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[icemel]

The only reason we would ever use cursors is to process data row by row when the processing can't be done on an entire result set.

Sometimes this can't be avoided.

Here's an example:

FETCH NEXT FROM #email_cursor INTO @name, @email
WHILE @@FETCH_STATUS = 0
BEGIN
	IF @Header = ''
		SET @Header = @name + ' <' + @email + '>'
	ELSE
		SET @Header = @Header + ', ' + @name + ' <' + @email + '>'
	
	FETCH NEXT FROM #email_cursor INTO @name, @email
END

if you can think of a way to get around this, I'd be interested to hear it.

Thanks

 

[gmmastros]

In the example you show, it appears as though you want to make a comma delimited list of email addresses. This can be accomplished like this....

[green]-- The @Temp table is for demonstration purposes only[/green]
Declare @Temp Table(Email VarChar(50))

Insert Into @Temp Values('email1@nonexistent.com')
Insert Into @Temp Values('email2@nonexistent.com')
Insert Into @Temp Values('email3@nonexistent.com')
Insert Into @Temp Values('email4@nonexistent.com')
Insert Into @Temp Values('email5@nonexistent.com')
Insert Into @Temp Values('email6@nonexistent.com')
Insert Into @Temp Values('email7@nonexistent.com')

[green]-- Query goes here
-- Replacing @Temp with your table name[/green]

Declare @Output VarChar(8000)
Set @Output = ''

Select @Output = @Output + '<' + Email + '>, '
From   @Temp

If Right(@Output, 2) = ', '
	Set @Output = Left(@Output, Len(@Output)-2)

Select @Output As CommaListOfEmails

If I've misunderstood what your code is doing, then please post some sample data and expected results.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[icemel]

There is no output variable in this sp.

Select @Output As CommaListOfEmails

Not sure what the above is for?

 

[gmmastros]

In a stored procedure, you can have sql commands that don't return a result to the client. In this case, I assumed you wanted to get a list of email addresses that were seperated by a comma. The problem is, you have each email address stored in a seperate record. The code I showed is a method for concatenating data from multiple records in to a single declared variable.

You didn't really show enough information in the post for me to provide you with a complete solution. It was the technique I was trying to show. You should be able to copy/paste everything in that code block to a Query Analyzer window and run it. After running it, you would see a recordset that has 1 record and 1 field. The data within the field would be a comma seperated list of emails.

I hope I explained myself well enough. If not, please let me know and I will attemp to clarify my explanation.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[icemel]

Thanks for your response.

At this point, we are not going to remove any of our cursors. So, what I need to find out is, the most efficient way to run the cursors.

So far, it looks like "forward_only" and "static" are most efficient, but then it looks like many factors can play into it, for example size of the result set returned.

 

[gmmastros]

icemel

Since I haven't written a cursor in several years, I am unable to help you. I strongly believe that SQLSister's advice was the best. Her advise was to remove the cursors. I showed you a method you could use to remove cursors also. Since you plan on sticking with them, I won't be of much help. Sorry. I apologize if I wasted your time.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom