Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Looping through records returned in SQL server 2005

Posted on 2007-03-17
Medium Priority
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 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

704 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