• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Looping through records returned in SQL server 2005

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,

  • 4
1 Solution
stankstankAuthor Commented:
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.
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 ...
stankstankAuthor Commented:
>> 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.
stankstankAuthor Commented:
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.


stankstankAuthor Commented:
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: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22457492.html
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now