Exec problem
-
Please read this, there are a lot more pitfalls to dynamic sql
.The Curse and Blessings of Dynamic SQL
And please revisit the original design
. -
And sometimes, you must resort to the "create a table for each user" solution, no matter what anybody says.
-
When is that (sorry never had that happen to me)?
-
I once worked with a web application that allowed managers to set accessibility to work units based on pick lists. For example,
User A can see orders that have Oranges and Apples, but not Bananas. User B can see orders that have anything but Bananas. User C can only see Bananas
In addition, User A can only see orders where the Customer's last name is between F and R.
Furthermore, User B cannot see orders that will be paid for by Check.
User C cannot process any orders for customers that have a credit balance greater than $500.00, except for customers in California, as long as they are shipped C.O.D. via U.P.S.
That is just for Company A.
There is also Company B. And C and so on, each with a different set of managers and business rules.
And there are 400,000 orders in the system at any one time.
The logic required to calculate the accesibility for each user for each work unit was such that it was prohibitive to calculate the list every time the user wanted to see a screen that listed the Nth page of 10 units.
Since there were many users all trying to click "refresh" at the same time, we placed each user's list in their own table, after trying to get them to share the same "work unit list" table. Even with a single denormalized table, there was too much contention for the data.
So, we did it and it worked wonders.
That's why I almost always say "almost never say 'never'".
jg
-
Why would you ever want to quit a job like that?
I wasn't born stupid - I had to study.
-
It was quite the best job I've ever had, despite the complexity of what we had to accomplish. And we weren't really selling fruit.
We got purchased and asked to move to <location removed to protect the guilty> or go elsewhere. The new company already had enough rocket scientists so I chose the latter.
And they turned out to be the other kind of rocket scientists anyway...
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply