Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Dynamic SQL - which would you use? Expand / Collapse
Posted Friday, January 4, 2013 3:35 PM



Group: General Forum Members
Last Login: Saturday, December 3, 2016 5:18 AM
Points: 45,619, Visits: 44,147
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

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


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

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

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

Post #1403168
Posted Monday, January 7, 2013 9:12 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 3,852, Visits: 6,567
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."
Post #1403691
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse