Eliminate Cursor

Question

Eliminate Cursor

Viewing 4 posts - 16 through 18 (of 18 total)

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

Cadavre (8/21/2013)
kapil_kk (8/21/2013)
Yes, I have used MERGE before but not in much depth like this....
USING (SELECT a,b,c
FROM @tblTempTable) [Source](a,b,[bit]) ON [Target].AccountOfficeId = [Source].a AND
[Target].PermittedOfficeId = [Source].b
after giving an alias to a source you define column names in braces
[Source](a,b,[bit]) what does this means?
I wanted to make it easier to follow the conversion of the cursor to the set-based approach. As you assign "c" to the variable @bit, I assigned "c" to the alias "bit".

thanks a lot Cadavre for your support....:-)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Cadavre (8/21/2013)
kapil_kk (8/21/2013)
Cadavre (8/21/2013)
kapil_kk (8/21/2013)
Koen Verbeeck (8/21/2013)
Sure. This is one of the examples where you don't need a tally table.
You can replace the logic beneath @bit = 1 with one MERGE statement.
The logic beneath @bit = 0 is one single UPDATE statement with a FROM CLAUSE.
BUt still cursor will be there....
Nope. The MERGE statement that I posted does everything the cursor does, as I see it.
Thanks a lot Cadavre 🙂
Can you please explain me what you did so that I can follow this thing to other cursor?
Especially in USING what you have done is not clear to me...
Have you used MERGE before?
Take a look at the video from the talk "Everything you always wanted to know about MERGE" by Hugo Kornelis from SQLBits XI, it'll be a good introduction.

Now that was a great presentation. Watch and enjoy the show, Kapil, and work through a few examples.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

ChrisM@Work (8/21/2013)
Now that was a great presentation. Watch and enjoy the show, Kapil, and work through a few examples.

I enjoyed it, found some of the questions in the audience to be a bit irritating. As soon as I got back from Nottingham, I wrote up a "knowledge share" article based on this talk, including links to the video, which went down very well at work. In fact, all of the "knowledge share" articles based on my experiences at SQLBits have gone down quite well, so it's probably about time for me to write up a few more from my notes 😀

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Cadavre (8/21/2013)
ChrisM@Work (8/21/2013)
Now that was a great presentation. Watch and enjoy the show, Kapil, and work through a few examples.
I enjoyed it, found some of the questions in the audience to be a bit irritating. As soon as I got back from Nottingham, I wrote up a "knowledge share" article based on this talk, including links to the video, which went down very well at work. In fact, all of the "knowledge share" articles based on my experiences at SQLBits have gone down quite well, so it's probably about time for me to write up a few more from my notes 😀

Now that's interesting - I've not yet had the pleasure of working in an environment where knowledge share was common. The closest was a Foxpro to SQL Server conversion around 4 years ago where the resident Foxpro guys - with Foxpro SQL but little or no SQL Server experience - were writing quite a few of their stored procedures using WHILE loops or cursors. One of Jeff Moden's articles, I forget which, found it's way into the senior dev's inbox, and shortly afterwards there was an internal dev seminar where the buzzword for the day was RBAR. Job's a good 'un 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden