What specifically should requester do then?

What specifically then should one do when a table has, say, 50 columns that can be arbitrarily filtered by users in self-directed queries, and you have to provide the supporting code?

CELKO (1/4/2013)

---

As I said, read books on basic Software Engineering. Yourdon and Constantine's STRUCTURED DESIGN is still classic. Chapters 6 and 7 are on Coupling and Cohesion. But you can find almost any book that will cover these fundamentals.



Post DDL, sample data and clear specs. He did none of this. The problem is that he has no foundations. It is impossible to give him the education in a forum. The short answer is that you do not write code that allows a random stranger arbitrary access to data at run time. You need to have specs.

Let me ask:

1) Why do want to write a declarative language like SQL with procedural flow control? Do you like cursors? Loops? Recursive CTEs, which are both?
2) Why are bit flags, loose cohesion and tight coupling a good way to program?
3) Why would you have a design so bad that you need dynamic SQL? Are data element names changing at run time? Is the name of the procedure “No_Idea_What_I_Am Doing_Until_Run Time”? Or (more likely) are you doing display formatting the database layer and not the presentation layer?

CELKO (1/4/2013)

---

[quote]3) Why would you have a design so bad that you need dynamic SQL? Are data element names changing at run time? Is the name of the procedure “No_Idea_What_I_Am Doing_Until_Run Time”? Or (more likely) are you doing display formatting the database layer and not the presentation layer?

tony@kenny.net (1/3/2013)

---

My question: Which way do you swing? Do you have any other ways? Any other comments welcome.

DECLARE @id INT
SET @id = ISNULL(@id,0) 
IF @id < 0 SET @id = 0

UPDATE myTable 
SET ClearFlag = 1
WHERE	
    (ID = @id)
    OR
    (ClearFlag = 0 AND @id = 0)

We're giving "power employees" of the company access to the company's data to derive business benefit.

CELKO (1/4/2013)

---

The power employees I work with regard me as the "Data Base Utility" who can get the raw or nearly-raw data in SAS data sets, assorted GIS formats, spreadsheets, etc. for their specialized power tools. That is why they are power users. My job is to hand them data that is clean, correct and immediately input into whatever they do. A lot of times their tasks are over my pay grade and security level.

The average internal user only needs a fixed set of queries that match to his security level and organizational scope. These days, I hand off the data to a report server. This tier has changed the game quite a bit. Now there is a "report person" who slices, dices, rapes and pillages that data to make it look pretty.

Middle managers at this level all claim that they need to see the same data in different colors, arranged in some special way, etc. as away to assert their personal power within the organization. Years ago, these guys would use FedEx for routine mail, have a FAX machine on their desk, demand huge green bar printouts, a reserved parking space, etc.

Someone needs to write a book on report servers. I briefly worked for a company that made a Cube of basic Community College data, and then sold custom reports from it to the schools. That was their product.

The external user gets an even more limited set of specific reports, such as your month account statement. On the bank's website, with a short drop-down list.

Me, the accountants and the lawyers do not believe in letting people romp thru the data base.