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 http://75.33.104.230/public/db_queues.png for a clear picture of this.

Thank you 21 times,

StankStank
stankstankAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
0
LowfatspreadCommented:
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 ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
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.

Thanks,

StankStank
0
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.