IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New trying to avoid creating spaghetti code here
So they have a stored procedure that has this:


SELECT U.UserID,
U.FirstName,
U.LastName,
|
V
(20 more columns)
|
V
,"HEADER A" =
CASE
WHEN (SELECT C2ID FROM TBLUserProgramPlan IUPP WHERE IUPP.C2ID = 7 AND IUPP.UserProgramPlanID = UPP.UserProgramPlanID) > 0 THEN C2AwardStatus
WHEN (SELECT C3ID FROM TBLUserProgramPlan IUPP WHERE IUPP.C3ID = 7 AND IUPP.UserProgramPlanID = UPP.UserProgramPlanID) > 0 THEN C3AwardStatus
WHEN (SELECT C4ID FROM TBLUserProgramPlan IUPP WHERE IUPP.C4ID = 7 AND IUPP.UserProgramPlanID = UPP.UserProgramPlanID) > 0 THEN C4AwardStatus
WHEN (SELECT C5ID FROM TBLUserProgramPlan IUPP WHERE IUPP.C5ID = 7 AND IUPP.UserProgramPlanID = UPP.UserProgramPlanID) > 0 THEN C5AwardStatus
WHEN (SELECT C6ID FROM TBLUserProgramPlan IUPP WHERE IUPP.C6ID = 7 AND IUPP.UserProgramPlanID = UPP.UserProgramPlanID) > 0 THEN C6AwardStatus
WHEN (SELECT C7ID FROM TBLUserProgramPlan IUPP WHERE IUPP.C7ID = 7 AND IUPP.UserProgramPlanID = UPP.UserProgramPlanID) > 0 THEN C7AwardStatus
WHEN (SELECT C8ID FROM TBLUserProgramPlan IUPP WHERE IUPP.C8ID = 7 AND IUPP.UserProgramPlanID = UPP.UserProgramPlanID) > 0 THEN C8AwardStatus
END
|
V
(a dozen more CASE statements)
|
V
FROM TBLUserProgramPlan UPP, TBLUser U, TBLUserProgramActivity UPA
WHERE UPP.UserProgramActivityID = UPA.UserProgramActivityID
AND U.UserID = UPA.UserID
AND U.EmployeeUSRID 'HRSYS'
AND U.UserStatus = 'ACTIVE'
AND UPA.ApprovalStatus = 'APPROVED'
AND ProgramStatus 'CLOSED'
AND UPA.ProgramID = 2
ORDER BY LastName, FirstName DESC



This query returns anywhere from 1,500 - 2,500 rows.

Now they've added a new Exception table to the database which contains 11 - 12 people. If the userid in the query is in the Exception table, every column that has a CASE statement needs to be checked so that if its value is "A", "B" or "C" then return "EXCEPTION"; otherwise, leave as is.


Trying to use a BEGIN-END block for all of the CASE statement columns throws a syntax error. I'm beginning to think I'll have to use a cursor on the resultset, putting each row into separate variables, then iterate through it checking the userid and awardstatus variable. Then insert the variables into a table variable and finally do a Select on the table variable.

can anybody offer an easier way to do this?




Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous.
- - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
New Agree with pulling the logic into a cursor
Sql sucks for that type of logic.

Far better to write some clean understandable code than torture sql to do it.
New can't do it in code
the subroutine calls the stored procedure, then builds an html representation of an Excel spreadsheet and binds the returned resultset to it.

I did what I wanted to avoid - use a cursor and insert the variables into a table variable. That way I can check the userid for each record returned from the query, then check the awardstatus fields to determine if they need modifying. My version of the stored proc takes about 1 second longer than the original. They'll just have to live with it.




Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous.
- - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
New No clean way out without doing TBLUserProgramPlan over
Those columns smell as if something that should have been a set of values in a table was serialized instead.

Depending on the overall circumstances and permissions, could you build a function to tear it apart in to the necessary temp tables and use those to clean up the query in the current stored procedure?
New Agreed, that table structure gives me the willies
--

Drew
New that table has been around for years
so I've learned, and columns have been added to it since it was created. Plus numerous programs access it so I can't do anything to it (after all, I'm just a contractor, not an employee).

Even better, the woman who wrote most of the ASP.Net applications and designed all of the tables in the database over a 12 year period left a few months before I was brought in, and the guy who her responsibilities dumped on him is still learning how all of the apps work. It makes it tough to ask a question to someone who may not even have touched that program yet.




Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous.
- - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
New (This sounds a lot like a metaphor for the 'post-Drumpf-Era'); savor the comparo, eh? ;^>
New I'm just savoring the *idea* of the 'post-Drumpf-Era'
--

Drew
New I'd be tempted to see if I can do something with a sub-query.
It might be possible to squirrel away the multitudinous lookups with a heap of left joins in a sub-query.

People who live and breathe SQL often don't know or forget about things like Don't Repeat Yourself. But then, SQL often makes that hard to do.

Wade.
     trying to avoid creating spaghetti code here - (lincoln) - (8)
         Agree with pulling the logic into a cursor - (crazy) - (6)
             can't do it in code - (lincoln) - (5)
                 No clean way out without doing TBLUserProgramPlan over - (scoenye) - (4)
                     Agreed, that table structure gives me the willies -NT - (drook) - (3)
                         that table has been around for years - (lincoln) - (2)
                             (This sounds a lot like a metaphor for the 'post-Drumpf-Era'); savor the comparo, eh? ;^> -NT - (Ashton) - (1)
                                 I'm just savoring the *idea* of the 'post-Drumpf-Era' -NT - (drook)
         I'd be tempted to see if I can do something with a sub-query. - (static)

You know nothing of this if they ask you...
55 ms