|
You could try using a table variable to essentially perform the operation.
Add the values to the table variable and join on the table. 
|
|
|
|
|
Or you could just use some standard SQL, with properly escaped parameters, which was my original point. Much simpler to develop and debug.
|
|
|
|
|
I disagree, I don't think you should be escaping characters yourself.
|
|
|
|
|
|
Different DBMS's have different escape characters and it's just not a nice way of doing it.
Paramaterized queries were created for a reason.
|
|
|
|
|
ScottM1 wrote: Different DBMS's have different escape characters
True. They also have different stored procedure syntax. Are you suggesting that's a good reason not to use stored procedures?
|
|
|
|
|
That's not what I'm suggesting at all.
If you were using stored procedures the procedure syntax would not change the way you call the stored procedures from your application.
If you were building SQL queries in your application and escaping it yourself different escape characters would force you to go through every single one and change it.
What happens if you forget to escape one of the arguments before you pass it?
Read this[^]
|
|
|
|
|
So if I change the database, I have to change one function (note: not "every single query") that escapes out strings. If you change database, you (possibly) have to rewrite every single stored procedure.
ScottM1 wrote: What happens if you forget to escape one of the arguments before you pass it?
Then you have a security bug.
Newsflash: If you don't write code correctly, it has bugs.
|
|
|
|
|
Electron Shepherd wrote: Then you have a security bug.
No, then YOU have a security bug, I don't have to worry about this.
The fact of the matter is you shouldn't be escaping characters yourself.
Parameterized queries were created for this purpose so why would you want to even do it yourself?
There are also performance benefits to using parameterized queries.
|
|
|
|
|
ScottM1 wrote: The fact of the matter is
Opinion, not fact.
ScottM1 wrote: so why would you want to even do it yourself?
And if you need to uyse and IN clause? What do you do then?
ScottM1 wrote: There are also performance benefits to using parameterized queries.
Really? As they say "state your source". Have you measured them?
|
|
|
|
|
Electron Shepherd wrote: Opinion, not fact.
Fact
Electron Shepherd wrote: And if you need to uyse and IN clause? What do you do then?
I already told you in a previous post.
Electron Shepherd wrote: Really? As they say "state your source". Have you measured them?
A parameterized query will use the same execution plan each time even if there are different arguments passed, adhoc statements will recompile each time different arguments are passed.
|
|
|
|
|
"The fact of the matter is you shouldn't be escaping characters yourself."
That is an opinion, held by you. You are perfectly entitled to hold that opinion. However, the one thing it is not is a fact.
ScottM1 wrote: A parameterized query will use the same execution plan each time even if there are different arguments passed, adhoc statements will recompile each time different arguments are passed.
Is that true? I don't see how it can be. I thought they were compiled each time, since a parameterised query is an ad-hoc query. All you are changing is how the variable parts of the query reach the DBMS.
|
|
|
|
|
It is true.
Parameterized queries result in prepared statements whose execution plan is re-used with different parameters, this is because it basically calls the system stored procedure sp_execute.
An adhoc query will only use the same execution plan if the query is exactly the same including all parameters and whitespace characters.
|
|
|
|
|
ScottM1 wrote: An adhoc query will only use the same execution plan if the query is exactly the same including all parameters and whitespace characters.
I can't speak for other DBMSs, but SQL Server is cleverer than that, and used auto-parameterization to allow different queries to reuse query plans. See http://www.benjaminnevarez.com/2010/06/auto-parameterization-in-sql-server/[^] for an example.
|
|
|
|
|
OK, I didn't know that it did that.
It will still only do it in a select few instances though, and looking at this[^] it will probably nearly never do it.
How often do you write a query that has no function calls, no GROUP BY statements, no sub-queries and also no joins?
|
|
|
|
|
ScottM1 wrote: How often do you write a query that has no function calls, no GROUP BY statements, no sub-queries and also no joins?
Apart from the joins bit, quite often. Single table queries only is a bit of a pain, though.
Interestingly, reading this[^], it looks like forced parameterisation removes the "single table" restriction.
|
|
|
|
|
That is interesting, the only downside appears to be that errors may be reported incorrectly.
I still think that if you want to query using parameters you should do it yourself, having the DBMS automatically changing your queries could end up in all sorts of funnys.
|
|
|
|
|
I go with Parameters all the way, even if I'm generating the SQL in Code.
The only downside of Parameters is that it's hard to grab the exact SQL (including values) that is being sent to the DB.
As for escape characters. On occasions when I have gone that route My escaping was always done inside a single function. There's no way I'd go around escaping SQL strings willy nilly.
In fact it goes further than that. When I'm building SQL in my App all contact with the Database is filtered through one specific DB class which wrapps ADO or whatever.
So the argument that you might "forget" to escape a particular query doesn't really stand up if you are doing this stuff correctly.
-Rd
Hit any user to continue.
|
|
|
|
|
What kind of query do you have in mind here?
Wout
|
|
|
|
|
|
Sql debug is always a problem no matter you use stored proc or straight sql statement in code. I see several advantages of using stored proc. It has faster execution. It is safe (against sql injection). Data/business logic can be modified in a stored proc alone without re-compliling the main application as long as the returned data columns are the same.
TOMZ_KV
|
|
|
|
|
Tomz_KV wrote: It has faster execution.
Not so much. Certainly not as much of a performance improvement as some would have you believe. There are situations where perhaps doing work on the DB server saves trips over the wire. In those cases yes. But for side execution of the same query, I wouldn't let performance influence me.
Tomz_KV wrote: It is safe (against sql injection).
The old SQL Injection argument is an iteresting one.
As I said above I believe at least 80% (probably more) of apps can get by quite nicely without the benefits that Stored Procs etc bring.
If you are building an app where SQL injection might be an issue you are absolutely in the 20% (or less) category.
My point on this thread is about what should be the Default Model.
I absolutely have no problem with people who need the features of a DBMS and know how to use them doing so.
Tomz_KV wrote: Data/business logic can be modified in a stored proc alone without re-compliling the main application as long as the returned data columns are the same
Well that's just an abstraction layer. You can build abstraction layers any way you want. A DLL, a web service, whatever.
Generally speaking even if you use stored procs you shouldn't have business logic in there anyway. So really what we're talking about is abstracting away the Database structure, which you should be doing in any case.
-Richard
Hit any user to continue.
|
|
|
|
|
Tomz_KV wrote: It has faster execution.
Not necessarily. The execution plan for the stored procedure is not determined dynamically, so may not be appropriate for the query as executed against the current data.
Tomz_KV wrote: Data/business logic can be modified in a stored proc alone without re-compliling the main application as long as the returned data columns are the same.
Why is that an advantage? Why is changing a stored procedure to implement a logic change "better" than changing compiled-to-exe code to implement a logic change?
|
|
|
|
|
Electron Shepherd wrote: Why is changing a stored procedure to implement a logic change "better" than changing compiled-to-exe code to implement a logic change?
For a in-house program, if the developer is not avaialble for making changes and re-compiling, a database guy could easily achieve the same goal by modifying the storedproc. This may not apply to a commercial program but happens frequenly for a home-grown program.
TOMZ_KV
|
|
|
|
|
Tomz_KV wrote: For a in-house program, if the developer is not avaialble for making changes and re-compiling, a database guy could easily achieve the same goal by modifying the storedproc
But, if the logic is in the stored procedure, the reverse is also true:
For a in-house program, if the DBA is not avaialble for making changes, a developer could easily achieve the same goal by making changes and re-compiling
Why is one better than the other?
The main disadvantage I can see with your approach is that an executable that has not changed starts behaving differently. That can lead to some unnecessary bug reports, when there is no "obvious reason" for the change in behaviour
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
