True Or False?

Question

True Or False?

Eskandari

Ten Centuries

Points: 1078
More actions
June 17, 2014 at 11:19 pm

#309303

Hello every Body
Is below code is correct or incorrect:
DECLARE @DocNo nvarchar(20)
DECLARE @inMode bit
IF @inMode = 0 --All Document
DECLARE Doc_Cursor CURSOR FOR SELECT DocNo from Document
ELSE
DECLARE Doc_Cursor CURSOR FOR SELECT DocNo from Document WHERE DocNo BETWEEN @inDocNo1 AND @inDocNo2
OPENDoc_Cursor
FETCH NEXT FROM Doc_Cursor INTO @DocNo
WHILE @@FETCH_STATUS = 0
BEGIN
---Some expressions
FETCH NEXT FROM Doc_Cursor INTO @DocNo
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27164 More actions · Answer 1

You have to declare missing variables

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Eskandari Ten Centuries Points: 1078 More actions · Answer 2

I Set the code in side SP And When I Run my code I see this error:

Msg 16958, Level 16, State 3, Line 257

Could not complete cursor operation because the set options have changed since the cursor was declared.

Phil Parkin SSC Guru Points: 246992 More actions · Answer 3

If you script the stored proc as CREATE from SSMS, you will see something like this at the start:

set ansi_nulls on

go

set quoted_identifier on

go

If you make sure that your set options are the same as those saved with the proc definition, you should avoid the error.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Even better would be to get rid of that cursor entirely. From what you posted I don't see a need for a cursor. If you want help to make this process faster than you ever dreamed possible post some ddl and sample data in accordance with the first link in my signature. We can most likely make that cursor go the way of the dodo bird.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 5

Sean Lange (6/18/2014)
Even better would be to get rid of that cursor entirely. From what you posted I don't see a need for a cursor. If you want help to make this process faster than you ever dreamed possible post some ddl and sample data in accordance with the first link in my signature. We can most likely make that cursor go the way of the dodo bird.

It's a real shame that I can't mark this as the correct answer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)