SQL2008 - Is there alternative to cursor to address my need?

Question

SQL2008 - Is there alternative to cursor to address my need?

BOR15K

SSCertifiable

Points: 5894
More actions
October 29, 2013 at 7:40 am

#285262

I have a table to scan and on each record to execute stored procedure. This procedure returns a value which I need to use to update table's column.
The process seems to be straight forward - create a cursor, fetch next, run stored procedure, update the column, fetch next, run stored...
Is there any better approach you would advise to look into, please?
Thanks for the ideas.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

If the stored procedure is converted to a function, you could use CROSS APPLY to call the function for each row.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

BOR15K SSCertifiable Points: 5894 More actions · Answer 2

That's interesting indeed, thank you!

Do you mean I can use it as a part of my update:

UPDATE myTable

SET myTable.Column1 = cApply.Column1

FROM myTable

CROSS APPLY (SELECT Column1 FROM myFunction WHERE ....) cApply

ON myTable.id = cApply.id

Thank you again!

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

I haven't done it myself, but I believe it will be possible.

Some examples on APPLY:

SQL Server CROSS APPLY and OUTER APPLY[/url]

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

BOR15K SSCertifiable Points: 5894 More actions · Answer 4

BOR15K

SSCertifiable

Points: 5894

October 29, 2013 at 8:35 am

#1662386

Thank you!

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

If you give the Stored Procedure code and the cursor code, we might be able to give a better solution. You should give DDL and sample data as well to work on it. 🙂

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

BOR15K SSCertifiable Points: 5894 More actions · Answer 6

Thank you Luis,

I am looking for the ideas at the moment. Want to play with various suggestions people may come up here, like CROSS APPLY for example.

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

If you're looking for ideas, this phrase by Jeff Moden might help you.

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.

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

Luis Cazares (10/29/2013)
If you're looking for ideas, this phrase by Jeff Moden might help you.
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.

To expand on what Luis said, I'd like to add a warning: Once you move to set-based code and tally tables, you'll never want to go back. You won't think of tables the same way any more and the performance gains will take you by surprise. 😀

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 9

Ed Wagner (10/29/2013)
Luis Cazares (10/29/2013)
If you're looking for ideas, this phrase by Jeff Moden might help you.
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.
To expand on what Luis said, I'd like to add a warning: Once you move to set-based code and tally tables, you'll never want to go back. You won't think of tables the same way any more and the performance gains will take you by surprise. 😀

+1 on that! It takes a different way of thinking, breaking the norm.... Try it, you will like it! 😀

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman