My quewtion is related to replacing loop, I am very interested on this. coz i really want to improve performance and I can’t find a way to replace cursor. Here is my requirement and i want if there is any way to replace cursor with Tally Table, let me know.
I have a table that has many fields and one is bit, there is a nightly job that checks this tables bit column if found 1 then it iterates and send other columns data to an Stored procedure, that SP checks if there is any session found between that parameter it calls another Stored procedure that is basically calling a C# (3.5) dll which recovery some information and dump in other tables.
so here is some graphical presentation
1- select startTime, endTime, schedule from tbl_Schedules where bit=1
2- EXEC SP_FindUserSession @startTime, @endTime, @schedule
3- EXEC SP_CallDLLToPostUserCredit (@SessionID, User)
at the Top, I am using CURSOR_tbl_Schedule, and iterate then send information to Step 2, that is also Finding Session (Thousands of users session hit during that startTime and endTime) and then finally i send those sessions with the help of CURSOR_Session to SP that recovers Users Credit and Update Users marks in specific table.
I would like to know if i can replace cursor. Right now I am in testing phase and planned to implement on production. But I really worried about those cursors, that may slow down performance.
If you have any questions or require more details, let me know.
I hope there is a way to solve this via Tally Table logic.