August 21, 2013 at 12:59 am
Hi all,
I have given a scenario in which I have to eliminate cursors from all the objects of database.
In my database I have around 300 stored procedure in which CURSORS are used.
So, can you guys please tell me what approach should I use to remove those cursor?
Also, can I remove the cursors using Tally table concept?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 21, 2013 at 1:17 am
It depends 😀
It depends on what the cursor is used for.
For example, if you need to run a query for each table of the database (e.g. to update statistics, or to compress data), a cursor is the way to go. You could implement it with a WHILE loop, but it would just be the same thing.
If the cursor is used on data itself, you probably can convert it to a set-based solution. A tally table might be needed, but is not necessary in all cases. I converted cursors to set based solutions before without using a tally table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 21, 2013 at 2:10 am
Hi Koen,
here is one sample of cursor that is used in one of the object:
DECLARE curApplication CURSOR FAST_FORWARD LOCAL FOR
SELECT a,b,c FROM @tblTempTable
OPEN curApplication
FETCH NEXT FROM curApplication
INTO @a,@b,@bit
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @C=COUNT(*) FROM table1 WITH(NOLOCK) WHERE AccountOfficeId= @a AND PermittedOfficeId=@b
IF @bit=1
BEGIN
IF @C=0
BEGIN
INSERT INTO table1(AccountOfficeId,PermittedOfficeId,AddedBy,DateAdded,ChangedBy,DateChanged)
VALUES(@a,@b,@p_strUserId,@dtTodaysDate,@p_strUserId,@dtTodaysDate)
END
ELSE
BEGIN
UPDATE table1 SET DeleteFlag=0,ChangedBy=@p_strUserId,
DateChanged=@dtTodaysDate
WHERE AccountOfficeId=@a AND PermittedOfficeId=@b
END
END
ELSE IF @bit=0
BEGIN
UPDATE table1 SET DeleteFlag=1,ChangedBy=@p_strUserId,
DateChanged=@dtTodaysDate
WHERE AccountOfficeId=@a AND PermittedOfficeId=@b
END
FETCH NEXT FROM curApplication
INTO @a,@b,@bit
END
CLOSE curApplication
DEALLOCATE curApplication
can this convert into set based approach?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 21, 2013 at 4:00 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 21, 2013 at 4:09 am
I'd do something like this: -
MERGE INTO table1 [Target]
USING (SELECT a,b,c
FROM @tblTempTable) [Source](a,b,[bit]) ON [Target].AccountOfficeId = [Source].a AND
[Target].PermittedOfficeId = [Source].b
WHEN MATCHED AND [bit] IN (0,1) THEN
UPDATE SET DeleteFlag=CASE WHEN [bit] = 1 THEN 0 ELSE 1 END,ChangedBy=@p_strUserId,DateChanged=@dtTodaysDate
WHEN NOT MATCHED BY TARGET AND [bit] = 0 THEN
INSERT (AccountOfficeId,PermittedOfficeId,AddedBy,DateAdded,ChangedBy,DateChanged)
VALUES([Source].a,[Source].b,@p_strUserId,@dtTodaysDate,@p_strUserId,@dtTodaysDate);
August 21, 2013 at 4:17 am
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....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 21, 2013 at 4:20 am
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.
August 21, 2013 at 4:29 am
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...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 21, 2013 at 4:31 am
Can anyone tell me from where I can learn about this SET Based approaches?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 21, 2013 at 4:34 am
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.
August 21, 2013 at 4:35 am
kapil_kk (8/21/2013)
Can anyone tell me from where I can learn about this SET Based approaches?
Aw man, that is a loaded question. Books, blogs, BOL and practise would be my answer.
August 21, 2013 at 4:39 am
kapil_kk (8/21/2013)
Can anyone tell me from where I can learn about this SET Based approaches?
Try reading articles by Jeff Moden.
The key here is to avoid cursors and WHILE loops, and just work with regular SELECT, INSERT, UPDATE and MERGE by joining tables together.
To quote Jeff: "Try thinking what you would want to do with a column, instead of with a row".
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 21, 2013 at 4:42 am
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.
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?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 21, 2013 at 4:45 am
Koen Verbeeck (8/21/2013)
kapil_kk (8/21/2013)
Can anyone tell me from where I can learn about this SET Based approaches?Try reading articles by Jeff Moden.
The key here is to avoid cursors and WHILE loops, and just work with regular SELECT, INSERT, UPDATE and MERGE by joining tables together.
To quote Jeff: "Try thinking what you would want to do with a column, instead of with a row".
thanks Koen 🙂
I will work on it...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 21, 2013 at 4:47 am
kapil_kk (8/21/2013)
Yes, I have used MERGE before but not in much depth like this....
USING (SELECT a,b,cFROM @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".
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply