Looping through records returned in SQL server 2005

Posted on 2007-03-17
Last Modified: 2010-03-19
Dear Experts,

Just as I thought everything was going really well, I ran into a problem when developing my application...  This part of the application deals with telemarketers and they are constantly updating and retrieving new contact's to call.  Who they call is setup through what we call a Queue.  Here is the meat of the problem:

Users are assigned to queues and each queue for the user to call has a priority.  We want the queues with 1 to be called first, and the higher number to be called last.  I don't want to try to Preload the buffer for the highest queue every time the telemarketer pushes the button to get a record if there are no contacts which meet the queue's criteria.  For this reason, the columns [LastQueryRun], [CurrentPoolSize] and [CurrentBufferSize] come into play.  Each time [AppMarketing].INS_PreloadQueueBuffer is called, it will update [LastQueryRun] and [CurrentPoolSize].  If the poolsize is 0 and the last query ran was less than 45 minutes ago, we should skip this queue.  However, if the poolsize is 0 (or any number for that matter) and the lastqueryrun field was MORE than 45 minutes ago, we should execute [AppMarketing].INS_PreloadQueueBuffer.  This will update LastQueryRun and CurrentPoolSize.  So the problem is, when that happens, we don't know what the values are...we don't know if we should just return a contactID for this Queue or go to the next.  How can we go BACK on a select?!  This isn't a dataset or anything!  What should happen is that we pull the [CurrentBufferSize] field and if it's <> 0 then we return a contactID from it...  If not, we do the same thing to the next queue.

[AppMarketing].QueuesBuffer has a trigger which counts it's records for the FK_Queue_ID which was inserted or deleted and updates that count in [AppMarketing].Queues.CurrentBufferSize.  This helps us to always have an accurate count of what is in the Buffer to know whether or not we should preload (populate) the buffer by calling [AppMarketing].INS_PreloadQueueBuffer

At this point, you are probably saying to yourself 'why does he have a QueuesBuffer table anyway?'  Well, the reason I added this is because I want a quick pool of ContactID's to grab from when a telemarketer needs someone to call.  Coming up with this information has quite a bit of overhead as the stored procedure [AppMarketing].INS_PreloadBuffer will have to search through anywhere between 3 to 15 tables depending on the criteria the administrative user has specified in the Queue.  

In summary, I already have the most of this done, all I can't do is figure out how to loop through the assigned Queues for the selected user...  I have started over on this portion of this stored procedure, but the big stored procedure [AppMarketing].INS_PreloadQueueBuffer is already completed!  yay!

The only parameter this stored procedure (the one that loops through the queues) should accept is @UserID.  it should determine the @QueueID parameter based on the QueueID of the current record.

Please see the screen shot at for a clear picture of this.

Thank you 21 times,

Question by:stankstank
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4

Author Comment

ID: 18739666
By the way, a user will never be assigned to more than four or five queues at a time...  When a queue has no more contacts, they should go to the queue with the next highest priority.
LVL 50

Accepted Solution

Lowfatspread earned 500 total points
ID: 18739762
sorry its not clear why you "loop" through anything for the user...

surely you just select the highest priority item available for them each time?

you only have 1 queue table?
with the queues identified on that...?

you need to ensure that your transactions are of an appropriate length...
i'd have expected you to have an indcator on the queue to lock/ignore it when it was being re-built...

can you give some example data to illustrate the problem ...

Author Comment

ID: 18741172
>> surely you just select the highest priority item available for them each time? <<
I am assuming you mean the highest priority queue.  If so, the yes, I want to choose the highest priority queue, but I don't want it being ran every single time the user calls for a record if there are no more records available in this queue.

>> you only have 1 queue table? with the queues identified on that...? <<
I don't understand this question - can you re-ask it?

>> can you give some example data to illustrate the problem ... <<
Absolutely!  Let me give it a shot here:

Queue 1 was added and has a pool of 375 contacts (LastQueryRun < 45 Minutes ago)
Queue 2 was added and has a pool of 0 contacts (LastQueryRun > 45 minutes ago)
Queue 3 was added and has a pool of 217 contacts (LastQueryRun < 45 Minutes ago)

User1 was assigned to the following queues/priorities
Queue 2/Priority 1
Queue 3/Priority 2

When User1 logs into the application, the procedure will check and see if there are any records in Queue2.  It finds that the LastQueryRun date is greater than 45 minutes ago.  Because of this, it executes [AppMarketing].INS_PreloadQueueBuffer @QueueID which preloads the buffer.  When this procedure is called, it also updates the currentpoolsize column and the LastQueryRun column.  

Because of this, I need to check the SAME queueID and see if there is a record to pull.  If it is STILL zero, then we need to go to the next QueueID which the user is assigned and do the same thing.  

That's where I am having the problem...  Calling [AppMarketing].INS_PreloadQueueBuffer, then returning to the Queue record to see what the updated fields are.

>> i'd have expected you to have an indcator on the queue to lock/ignore it when it was being re-built... <<
That is an excellent idea, and I can put it in.  Maybe a bit column called IsLocked or something.

Author Comment

ID: 18742310
I think I have made this question unnecessarily complicated.  I am going to close it, award you the points and open a new one.  I will post the link to the new question - I would appreciate your help with it.



Author Comment

ID: 18746714
Hey LowFatSpread - I have added the new question which I think is more to the point... I basically cut out all of the things which weren't necessary.  Please take a look at my question - I would love to hear your input!

I am awarding the points to this question to you - you have helped me on past questions and I appreciate it.

New question:

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question