Stored Procs, Packages, Views...Pah!
-
You could try using a table variable to essentially perform the operation. Add the values to the table variable and join on the table. :-D
Or you could just use some standard SQL, with properly escaped parameters, which was my original point. Much simpler to develop and debug.
-
True, but it rules out stored procedures...
You could use table value parameters to pass multiple values in one parameter, I don't use stored procedures for most projects though. Unless I am going to be performing the same query from 2 different applications or the query is extremely complex I always use paramaterized queries.
-
Or you could just use some standard SQL, with properly escaped parameters, which was my original point. Much simpler to develop and debug.
-
Why not?
-
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
-
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
-
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?
-
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.
This is NOT a valid reason for choosing one technology over another. Changing logic at the database level is a BIG deal. This isn't something you should be roping someone into simply because you can't find a handy developer. And hey! it's in the DB so a DBA should be able to handle it. Does the DBA actually understand the intent of the original code? Or the full implications of changing it? Or is he JUST FOLLOWING ORDERS? This is exactly what I'm talking about. We treat a DB implemented API like it's some slow cousin from the country that can be pushed around and manipulated by anyone who can boot up TOAD. It's Code dammit. It deserves the same amount of planning, control, respect and fear that VB or C# or C++ code commands. The technical aspects of making a change to some VB isn't really all that different to changing a Stored Proc. Sure, deployment "might" require a little more effort, depending on your set up. Technically making the change and deploying it isn't the bit that causes problems. It's the unforseen side effects of the change that will have you at your desk cursing at 8pm on a Friday night when your family is at home waiting for you. And if it's your app that's breaking, the DBA that was just following orders isn't going to be by your side on Friday Night. -Rd
Hit any user to continue.
-
Warning: The Following is a Rant. From time to time I get into the religious 'To Stored Proc or not To Stored Proc' argument. Let me confess right now. As much as I appreciate all the pro's of Stored Procs, I still prefer to not use them when it can be avoided. It may be a technically inferior solution but I still prefer to create functions and subs in my VB or C# code that perform the equivalent task. I have finally decided that the reason for my bias is that the greatest Code Horrors I deal with now are badly written Packages and Stored Procs etc. It's a bloody nightmare. And to top it all, I regularly have to deal with the fact that the features needed to debug aren't installed, or they're running some cut down version of a DBMS. So I'm debugging this rats maze of code using techniques that I haven't used since I wrote Basic on my Sinclair Spectrum. Plug in Values. Run. Check Log File. Nothing Happened. Plug in Values. Run. Check Log File. Something unexpected happened. repeat to fade. Right now I'm trawling through the PL/SQL code of a very large globally known company. Honestly I've decided that what I'm looking at is a cast off from that team of monkeys that are working on the complete works of Shakespeare. I also regularly have to deal with Databases that contain hundreds of functions, procs and views where nobody knows if they are needed anymore but everybody is terrified to modify or remove any of them. I see companies who spawn a new copy every time a proc needs to be changed (just in case) and use that. I see companies who have no Gold version of their DB. When they need a DB either for test or for a new Production site, they just copy an existing production site. As for version control. It seems DB Objects live in some abstract zone, like International waters that aren't covered by the treaties that cover version control. This is no way to live. Thanks for letting me get that off my chest. Now back to the rats maze. -Richard
Hit any user to continue.
First off: Thank you for your rant. It's a one based on experience. If if folks don't agree or are quick to have a comment: I appreciate your frustration. Now my turn for a comment or thought: I'll keep it general as possible. I went into a database seminar related to a now defunc product that was one of the best analytic tools I've ever used in my entire career.. and this was in 2001! I've yet to see it's match today. It was called "BroadBase" and it was awesome. Anyway; before they were bought and the software purposely taken off the market even though it didn't compete with the new owner's products... I went to a seminar they hosted. In that seminar we covered and discussed things like data-marts, data ware-housing, reporting, applications, various database vendors and design in general. Some of the major points I remember quite clearly was where do you put your business logic: In the database or in the application? Now there are folks who say one, the other or both. Here were the things that interested me: they aren't answers.. but they are good things to consider when deciding: 1: Placing Business Rules and logic into the database (foreign keys, PL SQL etC) to help deliver information and protect data integrity put more reliance on the particular Database vendor. (Puts responsibility for data integrity on the database's shoulders) 2: Putting business rules and logic into your application makes your application less dependent on the specific DBMS your using 3: If multiple applications use the same database: Option 1 seems like a good option but putting business rules into a shared code base all systems can leverage gives benefits of option 2 but might be impossible or too difficult to implement enterprise wide. So for me: I don't have an argument for one way or another. I'm also in agreement with other comments in this thread to the tune that it's not necessarily tools but skill that makes the difference in quality for finished systems. I also am slow to bash folks for ugly systems I'm asked to fix because without knowing what environment the folks were working under: budget; time; boss; requirements; chief architect passed away mid project... god forbid... My Opinion is: There isn't a one size fits all solution: consider ALL details; short term and long term goals of the project; consider your team, your budget, and make it happen as best you and your team can!
Know way too many languages... master of none!
-
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[^]
-
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.
-
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.
-
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:
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.
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.
-
First off: Thank you for your rant. It's a one based on experience. If if folks don't agree or are quick to have a comment: I appreciate your frustration. Now my turn for a comment or thought: I'll keep it general as possible. I went into a database seminar related to a now defunc product that was one of the best analytic tools I've ever used in my entire career.. and this was in 2001! I've yet to see it's match today. It was called "BroadBase" and it was awesome. Anyway; before they were bought and the software purposely taken off the market even though it didn't compete with the new owner's products... I went to a seminar they hosted. In that seminar we covered and discussed things like data-marts, data ware-housing, reporting, applications, various database vendors and design in general. Some of the major points I remember quite clearly was where do you put your business logic: In the database or in the application? Now there are folks who say one, the other or both. Here were the things that interested me: they aren't answers.. but they are good things to consider when deciding: 1: Placing Business Rules and logic into the database (foreign keys, PL SQL etC) to help deliver information and protect data integrity put more reliance on the particular Database vendor. (Puts responsibility for data integrity on the database's shoulders) 2: Putting business rules and logic into your application makes your application less dependent on the specific DBMS your using 3: If multiple applications use the same database: Option 1 seems like a good option but putting business rules into a shared code base all systems can leverage gives benefits of option 2 but might be impossible or too difficult to implement enterprise wide. So for me: I don't have an argument for one way or another. I'm also in agreement with other comments in this thread to the tune that it's not necessarily tools but skill that makes the difference in quality for finished systems. I also am slow to bash folks for ugly systems I'm asked to fix because without knowing what environment the folks were working under: budget; time; boss; requirements; chief architect passed away mid project... god forbid... My Opinion is: There isn't a one size fits all solution: consider ALL details; short term and long term goals of the project; consider your team, your budget, and make it happen as best you and your team can!
Know way too many languages... master of none!
JasonPSage wrote:
My Opinion is: There isn't a one size fits all solution: consider ALL details; short term and long term goals of the project; consider your team, your budget, and make it happen as best you and your team can!
You come on here being all reasonable and well thought out. We're trying to have an endless pointless quasi-religious argument here. Apart from being a spoil sport, you are of course, mostly right. Except for that bit about forgiving people. -Rd
Hit any user to continue.
-
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.
-
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.
-
"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.
-
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.