SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic SQL - which would you use?


Dynamic SQL - which would you use?

Author
Message
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19996 Visits: 7417
CELKO (1/4/2013)
What specifically should requester do then?


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.

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?


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?



There's no "random stranger". That's just an idiotic, pointless slur.

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

If you're incapable of really assisting with that in a real-world situation, just say so. Don't hide behind idiotic and irrelevant prattle.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228898 Visits: 46344
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?


He doesn't, no, no and no. (and there was nothing in the OP's question to suggest either of the latter two, nor is this a 'tight coupling' or 'loose cohesion' issue. This is a very common search pattern where users can enter any number of filters to search on (eg search by first name and/or last name and/or phone number and/or department).
The recommendation of dynamic SQL in this case is solely to overcome a shortcoming in the SQL query optimiser.

Did you even read the OP's question or did you just see the word 'dynamic' in the title and base your reply off that?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Steven Willis
Steven Willis
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2089 Visits: 1721
tony@kenny.net (1/3/2013)
My question: Which way do you swing? Do you have any other ways? Any other comments welcome.

FWIW, this is how I'd probably do it. I like to get rid of NULLs as early as possible in a procedure so I don't have to use "Where X IS [NOT] NULL" syntax. Sometimes NULLs are unavoidable and of course this snippet assumes an INT key > 0.



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)



ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19996 Visits: 7417
CELKO (1/4/2013)
We're giving "power employees" of the company access to the company's data to derive business benefit.


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.




The average internal user only needs a fixed set of queries that match to his security level and organizational scope.

Yes, but the "power users" -- as I explicitly referenced -- don't.

You always set up straw men to knock down while ignoring, and not comprehending, the essential points.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search