Removing a cursor

Question

Removing a cursor

PearlJammer1

SSCrazy Eights

Points: 8495
More actions
December 8, 2014 at 9:53 am

#295667

Hi
This is a question from a high level view about the possibility of removing a cursor from some of our tsql code.
Basically the steps are:
We create a temporary table, we open a cursor and fetch a number of input variables from various tables which are then pasted to a stored procedure and executed - the cursor then fetches the next lot of parameters fro input into the stored proc.
Could a CTE be used in this situation to do the same ?

Viewing 4 posts - 1 through 4 (of 4 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Your main problem is the stored procedure. Unless you change the code from the procedure, you'll end up with RBAR anyway.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101200 More actions · Answer 2

You could build a concatenated string containing all the executions, then EXEC() that string. The issue would be if errors occurred during processing.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

PearlJammer1 (12/8/2014)
Hi
This is a question from a high level view about the possibility of removing a cursor from some of our tsql code.
Basically the steps are:
We create a temporary table, we open a cursor and fetch a number of input variables from various tables which are then pasted to a stored procedure and executed - the cursor then fetches the next lot of parameters fro input into the stored proc.
Could a CTE be used in this situation to do the same ?

If you have a stored procedure that needs to be fed one set of parameters at a time, then you're considering fixing the wrong problem. The stored procedure needs to be fixed, not the cursor. There is no disadvantage to using a cursor for this particular task and a well written cursor will usually perform just as well or sometimes better than the use of explicit temp tables to do the same thing.

--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)