Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Dynamic SQL - which would you use? Expand / Collapse
Author
Message
Posted Friday, January 4, 2013 2:46 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:37 PM
Points: 1,945, Visits: 2,921
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?


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1403135
Posted Friday, January 4, 2013 2:52 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 2,044, Visits: 3,059
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1403136
Posted Friday, January 4, 2013 3:35 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
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

Post #1403151
Posted Friday, January 4, 2013 5:39 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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)


Post #1403168
Posted Friday, January 4, 2013 5:57 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:37 PM
Points: 1,945, Visits: 2,921
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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1403172
Posted Monday, January 7, 2013 9:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 2,044, Visits: 3,059
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1403691
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse