There are times when parameter queries won't work. They are not very flexible.

Let's say you make a custom query dialog, for flexible reporting. User enters one/more parameters: one/more customer names, a dollar amount, less than or greater than (e.g. order amounts <$100, or >$100), and one of several possible sort orders. You can build that complex query easily and briefly by building up a SQL string, but unless I'm mistaken, parameters won't do it. (Well, I suppose you could have many different parameterized queries, depending on which parameters the user chooses to supply, but that grows huge fast with permutations)

Also, the example you give is ugly code; bad primarily for being a classic jumble. The embedded functions ought to be separated out, assigned to temporary variables; this would ease both reading and debugging. It seems to me that although parameters are more technically correct, and will avoid a few types of problems, they're not nearly as elegant (quick and tidy and self-contained) as building the sql string. It doesn't take much to build the sql string correctly and safely, anyway.

Parameters are the the input values (and types) of a query and only that. What you are talking about is dynamically build a query, which may be valid in some cases. But never (never ever!) mix the value in that build, add it as a parameter.

__________________

John,
"Never (never ever!)" are very strong words. You give no reason or explanation. Passed parameters are a feature in ADO, which talk to a feature in (generic) SQL server. Just because the features exist doesn't make an imperative to use them. As I see it, SQL is an ancient language which was updated a couple decades ago to include parameter-passing. But again, just because the feature is there doesn't give it any advantage over more traditional (and less verbose, less cryptic) ways of using parameters.

I could be wrong, but I don't know of any compelling reason to avoid old-fashioned string building.

I think that IF the failure to use formal parameter passing caused:

• Big performance hits
• Unpredictable bugs
• Frequent incompatibility
• Sub-standard results

...then it would be good to avoid the simpler way, at least in some cases. But it appears to me that this edict of style generates from a devotion to Microsoft's database technology of the week, not from any compelling reason.

For example, in Access (JET) it was advisable to use formal parameters, because there was a performance hit with freeform queries. But if it's a rare routine that does it, or a small database, it doesn't matter. I think the base imperative is do whatever is simpler and more brief (if it won't cause problems).

I'm not saying your way is bad. It's probably widely preferred for enterprise projects. But I don't see any reason why it's an issue for "never", much less "never (never ever!)".

PS: If you get a chance, check out Cache.
See the demo videos. Here's a technology that makes a joke of SQL. I think it's pretty important to remember that the whole massive architecture of ADO is built around ancient and outdated technology; important to keep perspective on ideas of "correct".

There is still no excuse not to use a paramaterized query. Build the parameterised query dynamically if you really cannot think of anotehr way. Most searches can be made simple:

SELECT * FROM orders WHERE
(customerName = :pCustomerName OR :pCustomerName IS NULL) AND
(orderAmount < :pMinOrderAmount OR :pMinOrderAmount IS NULL) AND
(orderAmount > :pMaxOrderAmount OR :pMaxOrderAmount IS NULL)

Sorting is done in the client grid. To "wildcard" a parameter in this query, set it to null


If you have some good reason why this isnt possible, there is nothing stopping you using string concatenation to build up a parameterised query, and I do this in some of my apps. I have the user type in one or more fields on a form and their edits are put in a datatable. I then scan through the datarow, looking at the type of the column and the value the user typed:

THere is nothing stopping you offering huge amounts of flexibility to the user, but the crucial difference youre MISSING here is that by not using parameters youre shoeing everything through a string represenation and back. Precision loss, time zones, date formats.. Everything I talk about as to why putting a SQL together with straight concat is retarded and parameters are great STILL HOLDS





tempVars in an SQL? We arent writing stored procedures here, we are running an SQL..

Nonsense. Show me some of your vb code.. Go on.. I want to see that all your programs are written as one huge function and never use anything other than string variables and when you want to do something like add 2 numbers, you convert from string to number, add, and store the result back in a string.

Do you do this? Nope.. So please.. dont even try to tell me that storing everything in a string is simple, elegant, quick, tidy and selfcontained..

Again, rubbish. If string was perfect for representing everything, why do we even have other datatypes?

I appreciate your arguemnt, I just dont think you appreciate parameters..

__________________
DW1 DW2 DW3 DW4 DNU PQ

Go on.. show me that app of yours that uses nothing but strings.. "Just because DateTime exists, doesnt mean I have to use it" - true, but try convincing anyone here that you know what youre talking about in the programming arena if you constantly make such a short-sighted claim!

Er.. paramerterisation of SQL has been around as a notion, nearly as long as programming languages with typed variables have existed. Taking your stated timescale of "a couple of decades" - if you turned up to a job interview and said "Oh.. I havent updated my computer knowledge in more than 20 years; i still write SQLs like they did in the early '80s" do you think you'd get the job? Come on.. Quit with the fallacious argument, or seek out that one company out there whose payroll system is still programmed by conencting wires on a breadboard and go work for them..

I've given pretty much every advantage possible. I cant state the disadvantages because there arent any.

You arent using parameters! Your " '80s way" is representing everything as a string and then having the database either:
a) convert your value from string to native datatype and do the relevant comparison
b) convert every value in the table from native type to string, then do a string comparison

Do you even know what it's doing?


Another way to present this as daft, is to tell me, what date is this:
"030201"

Cmon.. what is it? 1st Feb 2003? 1st Feb 1903? 2nd Mar 2001? 2nd Mar 1901?

Here's an even more stupid one:
"191211"
11th Dec 1919? 11th Dec 2019? If the format was "yyyyMd" that could even be the 1st Jan 1912.

Representing a Date as a String is silly.. it leads to your "unpredictable bugs" list entry. Parsing it leads to your performance hit. Converting all dates in a million row table to strings really leads to a performance hit. Take your app to america where the users date format is different leads to your incompatibility.


Indeed.. try and think of a single advantage that extends beyond programmer laziness, for building SQLs out of concatted strings, and I'll shoot it down..

It's crap, for every reason I have listed in the original post, and the subsequent ones.. Hopefully the debate is proving highly educational to all who read this topic though



Parameterised queries have probably been around longer than Microsoft have had a database engine ; there is no slaving to microsoft here. Every vendor of every programming language makes the facility available becaise it is universally recognised and a good, proper way of getting data between systems. Essentially here youre performing Interprocess Communication, and you do not want to do that by relying on one systems parsing of another system's strings. We wont even talk about string encoding at the moment.


Thinking like that never raises the bar of quality; this is a personal pride issue for some..

..or a laziness issue for others

Thanks.. if you did, you wouldnt succeed

Umm.. well, youre free to do as you choose. When youre writig your next app all in one class, in the Sub Main, using only strings, I'll for sure not have any problem with you concatting your SQLs together in any old way that is easiest for you.. but dont ask me to hire you to direct my programming team

I'm aware that they are having a go at solving the O-R Impedance Mismatch but I dont feel it is particular relevant to this discussion, which is aimed at enabling better using of SQL within systems that are backed by an RDBMS

Important to keep perspective on the notions of scope and context w.r.t forum threads

__________________
DW1 DW2 DW3 DW4 DNU PQ

That doesn't follow at all from what I said.

Total non-sequitar. Doesn't even make sense, much less answer my argument. Rants are no substitute for reason.

Yep, and it doesn't amount to much in most cases. Certainly not enough to evoke an edict (much less ranting).

Indeterminate. You need to use YYYYMMDD format. Or a parameter would be fine also (as I said before). I'm not a coding bigot, I'm just pointing out that simpler is often just as good, if not better.

There's a big difference between laziness and keeping things simple. If you like typing half a kilobyte to code every little query, for no particular reason, go ahead. There are all kinds of things one can spend time doing/typing/nit-picking. I prefer to get things accomplished rather than inventing edicts for unnecessarily doing things the tedious way.


I don't envy you for putting your pride in intense conformity to excessive structure.

I'm not interested in fighting with you. I said what I had to say, I stand by it, and perhaps it will be educational for others, as you said.

Sure it does; youre asserting that String suits every need and specialist data types are unnecessary.. You also didnt actually respond to the point

Again, youre offering a boilerplate "um. no" without qualifying your reasoning - if youre going to present an opposing opinion, you need to back it up with something

Your chosen name gives away a little; perhaps concepts like type safety and code re-use arent something you considered in whatever prior programming experience you had before .net - i'm amazed that you'd gloss over performance benefits though; it's normally newer programmers accustomed to lots of computing resource that dont bother to code in optimal ways

Who says? Now youre laying down rules just like I'm saying "you need to use a DateTime parameter if youre dealing with dates/times. By mandating a specific string format to represent a date time, youre effectively enforcing a similar standardisation as using a type specific parameter, but youre incurring the conversion overhead. Youre also losing precision and location awareness. In some contexts this might not be important, but then you'll find yourself adding other rules and other formats to cope with situations where milliseconds or timezones are needed? Youre starting to clutter your code with an excess of representations when one sensible one exists, and your argument becomes increasingly non sensical.

Make everything as simple as possible, but no simpler. What's simpler:
Converting between strings and datetimes
Leaving datetimes as they are

?

I dont actually type most of the code; because parameterisation is regular, type specific and sensible, it is something that a computer can do very well. I write the statement, it picks out the parameters, typecasts them, sets them up and we're done. You unwittingly use a computer to write code everytime you drop a button on a form.. That code runs into hundreds of kilobytes, but you dont see it or realise it because it's hidden away, it's regular, strict and it works. It also outperforms (in the case of SQL statements) inline SQLs.

Addiitonally, though the concept may be new to you, it's is well encapsulated too. In your strive to "mkae things simpler" you might actually be making things more complex because youre filling your button handler code full of SQL statements - statements that may look pretty in your eyes, but they are in the wrong place, unsafe, slow and inefficient.


Do you drink instant coffee? Do you wash your clothes by hand? Do you find an ipod easier to carry than a symphony orchestra? Your life is filled with instances of machines and processes making it easier and simpler, and you have a choice not to employ those machines or processes and instead do it the long, hard way. Do you? Are you even aware that Visual Studio can write nearly all the code surrounding an SQL; all you have to do is write the SQL? Do you know that in cases where true dynamics are needed, that you can write code that will write the SQL?

Stick around the forums and read a little - you might come around to liking this parameterisation lark


You used the word "rant" before; just because I can think of more thinks to say and I put more words into the counter argument [in order to better educate other readers, including yourself] doesnt make it a rant, nor a fight. Standing by what you said is admirable, but if you dont actually back it up with some sensible logical argument, and spend your time nitpicking over my analogys and context descriptors then that stand wont actually count for much in the eyes of someone reading. I'm genuinely thankful for your input though, as may others be because it's evoked a wider discussion around the topic than I would ever have been bothered to write initially

__________________
DW1 DW2 DW3 DW4 DNU PQ

I do agree that parametrized queries are the best way... Most of the time... But I don't think programming has any invariable truth that no one must stray from. Here are a few examples I can think of where I would consider the alternative.

You want to use the filter from a DataView as the WHERE clause for your query. If you do control the filter on the Dataview, you can insure it always has a syntax that won't break the SQL and use it when it is simpler to do so.

You need to parse a single WHERE clause out of a whole form's worth of filters, some empty, some not.

You want to use a JOIN on a few other tables only when the additional data is actually used by a filter (I can only guess there might be a notable performance gain from this, but performance issues are difficult to guess right).

Obviously, this is bad :

but this looks fine :

The AddCondition method would be something like this :

I would actually think about creating a ConditionBuilder class to hide all this or maybe just SqlBuilder that would create the whole SQL query including the columns to return and the table for the FROM clause. It would be even simpler than to pass nulls when a criteria is not needed and could be used to generate the Filter value of a DataView independently from any database command.

Don't think I actually recommend anyone to start concatenating Strings for fun, but the parametrized query might force you to build a gigantic SQL Query that can accommodate the various null values for non existent filters out of the 20 filters you may have. That means a single String that's defined over 10 - 20 lines. Did you forget to put a space on the line break? Lots of things can go wrong. Less that the String concatenation, obviously, but still.

On the other hand, this method allows you to set the conditions you need in bits of 30 - 40 characters each. You can step through each line and see when the condition went wrong which makes it easier to debug than the parametrized query.

I admit it lacks typed parameters which would avoid your having to think about the " ' " character around and within strings and I have yet to work with dates that way. I haven't had the time to look at how the current parametrized queries do that yet, maybe you can use a Microsoft provided method somewhere. Otherwise you can just add value formatting static methods in the SqlBuilder class.

__________________
The human mind's capability to comprehend abstract concepts is limited to the vocabulary it can use to describe it. The more precise the building blocks, the more complex the thoughts that can emerge... Sounds like the evolution of programming languages doesn't it?

Mmhhhh, but answer me (i.e. defy) this:

If youre capable of concatenating a string on

strSql &= "colName = '" & txtCol.Text & "'"


Youre certainly capable of concatenating a parameter on:

strSql &= "colName = @colname"
cmd.Parameters.Add("@colName", txtCol.Text


-


I wont wind on about datatypes and the foolishness of trying to represent everything as a string yet again, but that's a major consideration for parameters.

You say there are no invariable truths, and for the most part, I agree, but one thing you should never do, because there is no good reason for it, and no situation that requires it, is use string-value concatenation to build a query SQL. String-parameter concatenation fine, but string-value concat; just dont do it! I'll give you that as an invariable truth. the only time it will actually fail is when something is not parameterisable, and in that case, there are a raft of reasons why we typically dont do that kind of thing (we prepare queries that the db can plan ahead)

(Are you sure you thought your argument through?)

ps; when youre concatting your where clauses on, its far more trick to just to this:

strSql = "SELECT * FROM table WHERE 1=1"

strSql &= " AND col = @col"
..
strSql &= " AND col10 = @col10"

__________________
DW1 DW2 DW3 DW4 DNU PQ

Of course... But it isn't actually just that. You have to add the OR @colName IS NULL, put parenthesis around this, etc. That gives a pretty complex SQL expression. Even in the event that you just search in ColA from the A to Z columns, you have 26 * 2 conditions and you have to put parenthesis and OR to make a non existent field work correctly.

And you still cannot see the values in the SQL query to debug it. Say you use a combo box to tell which color to look for and the default value "unknown" should be removed from the search because it is a special data (somehow). Now, you forgot to put the code for that says "Unknown" is actually null and you accidentally run the query with the value whatever the other search criteria are. The only info you have is that the search always return zero records.

You run the search with no filter and look at the query in the SqlCommand just before it is run. Would you rather see :

Then you will look at the parameters from the SqlCommand and look for one that is not null.

Or isn't easier to look at this :

If you don't understand what is wrong at that point (when the problem is still too complex), you copy paste this in a SQL editor and run it straight on your database until you pinpoint the problem.

It is true that parametrized query produce clean code to use and create, but you reach a similar point of simplicity by encapsulating the query creation in an object. Add a few static methods to convert the DateTime object or raw String to a String ready for adding to the query and you got something that is nearly as clean as the parametrized query. Without the complexity in debugging and the limitations.

--
One more situation I would think about this is when you want the user to use a CheckedListBox to check the categories he want to search for. Parametrizing a query like this :

is not really an option if the number of categories is variable (the categories are in a table). It may be possible to write a parametrized query like this :

and give it an array or collection in parameter, I must admit I never tried but I doubt it and the SqlCommand class is sealed so there is no way to add this with inheritance. The number of sealed classes and not virtual methods and properties is one of the thing I miss from Java btw...

--
Thanks, I didn't think of that!

__________________
The human mind's capability to comprehend abstract concepts is limited to the vocabulary it can use to describe it. The more precise the building blocks, the more complex the thoughts that can emerge... Sounds like the evolution of programming languages doesn't it?