does this make sense?

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 :)

 

Print | posted on Wednesday, January 07, 2004 8:42 AM

Feedback

# re: does this make sense?

left by Greg Duncan at 1/7/2004 11:01 AM Gravatar
If it works, works well, and meets the needs of your users then it's "good" in my book...
:)

Is it perfect? Nope, but nothing is. You'll look back on this code in a few years and say, "Gee... what the heck was I thinking?". I cringe when I look at my old SP's...

I like how you break the work down into specific steps. Should be much easier to debug/change that way.

The thing that comes to my mind when thinking about this is is there some way to do some initial filters when populating your temp Table?
ie. use some IF ELSE statements in the initial query and maybe eliminate your first DELETE step.

IF NeedWorkFlag = 1
BEGIN
INSERT INTO MYTEMPTABLE SELECT bla bla bla FROM bla bla bla
WHERE FollowUp > getdate()
END

ELSE
BEGIN
INSERT INTO MYTEMPTABLE SELECT bla bla bla FROM bla bla bla
END

This can be taken to the Nth degree where all the parameters are grouped into IF's...

But again, if it already works, then you are already a hero. Fix it when it NEEDS fixing, otherwise spend time on the other things your users need (happy users = more time to geek on other things :)

Just my 2 bucks...
Hope it helps a little,
Greg

# re: does this make sense?

left by Travis Laborde at 1/7/2004 11:36 AM Gravatar
Greg, thanks!

You're exactly right about the IFs... To me that looked like it would be much harder to read and maintain in the future, though it surely would be FASTER to execute.
Comments have been closed on this topic.