Recently, I've come up with an idea for handing a vexing situation, and though it's working very nicely, I wonder what you, my fellow SQL Server developers, will think about it.  Here's an example situation:

We have a business system we call "Work Queues."  It's for our ASP app, and it's pretty generic, in that if we feed it a sproc, it "builds the queue."  The queue simply allows a user to page through working a set of "accounts" or other such "items."  Part of the definition is that the sproc for a queue must return a few columns that the queue expects it to have, then is free to have it's own set of other columns as needed.

So, We have defined 4 new Queues to be set up.  The problem is, it's much more than 4 because of a few factors: a) We might want "all" the records that meet the basic criteria.  b) We might want to filter it to show only those that "need work" (based on a followup date).  c) We might want to filter it to show only those for a certain "rep."  Additionally, the first thing we have to do is display a "menu" which shows the 4 queues, and how many records "need work" and how many records there are total in the queue.  These numbers are displayed as links, so you click through to actually generate and enter the queue.  Whew!

So, how many sprocs would that be?  Too many, I'll tell you that.  One more thing:  Those of you who will tell me to use dynamic SQL need not bother.  Maybe.  One day.  Not today :)  So, here's what I've done:

I have created one sproc, with args like this: QNumber, NeedWorkFlag, RepID, CountOnlyFlag.

Then, in the sproc, I declare a TABLE variable with only a few basic fields.  Then I populate this table variable based on the QNumber (1-4 in this case).

Then, if the NeedWorkFlag is set to 1, I delete from the table variable where the FollowUp date is set for the future.

Then, if the RepID is nonzero, I delete from the table variable where the RepID is not equal to whatever this parameter shows.

Finally, if the CountOnlyFlag is set, I only return the COUNT of records from the table variable, else I link the table variable to a view to actually return the needed columns.

Whew!

So tell me, is this incredibly horribly bad?  It seems to be working great, but I'm left not knowing if this is a "good thing" or if the real guru's out there will tell me that I should go back to driving a Taxi.

By the way, my 3 year old son Carter started pre-school yesterday! Awwwwwwwwwwww :)