July 30, 2008 at 3:33 am
who writes query strings such as
cmd.Execute("exec InsertCust 1, 'hello'");
anyway?
with .NET it should all be done by the data access layer and integrated.
Even without a DAL it should use the sqlcommand object properly not with command type = text
eg
dim cmd as sqlcommand
Dim par1 As New SqlParameter("@par1", SqlDbType.varchar,10)
cmd.Connection = SqlConn
cmd.CommandType = CommandType.StoredProcedure
'Set the SQLString Variable To A Specified SQL Query
SQLString = "InsrertCust"
cmd.Parameters.Clear()
cmd.CommandText = SQLString
par1.Direction = ParameterDirection.Input
par1.Value = "hello"
cmd.Parameters.Add(par1)
Try
cmdStaff.ExecuteNonQuery()
catch etc
No string injection here, especially if input strings are checked before passing as parameters.
July 30, 2008 at 3:45 am
In a previous role, I worked with an architecture that expected you to declare name of the stored procedure in a code attribute at the top of the method - it was in C#. The advantage this gave was that at build time we added a post build task that used reflection to pick up these attributes and give us a list of warnings showing all the code that called stored procedures that weren't in the database that was built at the same time.
This saved a chunk of time during test!
July 30, 2008 at 4:27 am
I like stored procedures.
They provide a way of encapsulating complex sql code (ok so a 3000 line reporting procedure suggests there are bigger problems, but it's not my server, I don't make the rules - it's that or send the content of the stored procedure over the wire each time).
They allow you (with a proper naming scheme) to pinpoint issues more easily (you don't have to work out what that particularly cryptic statement is meant to be doing if it's in the name).
They allow the db to be black boxed - I can hand out access to others without them having to know ANYTHING about whats going on under the hood - so stored procedures act as an interface.
Users are lazy. Developers, Reporting users, DBA's. Everyone is a user in some sense. They will do what is easiest for them. This is normal. So as a design principle, I want the pain of screwing something up to fall on the person who is responsible for it and is able to fix it. If I give access to the underlying tables, someone else screws up and it falls on me. If I give them access to a stored procedure, it only screws up if I screwed up.
July 30, 2008 at 4:35 am
One big advantage to using stored procs that I have not seen mentioned here is when it comes time for a SQL upgrade. Without having all your sql code in stored procs, relying on and using a tool such as Upgrade Advisor becomes a major headache. Because all your sql code doesn't reside in the database, if you want to be thorough -- and who doesn't when you're upgrading to a new version of SQL? -- you'll have to create an all-encompassing trace file from each of your applications ensuring that no sql statement is overlooked. That's no simple task, at least not where I work. Using stored procs is not a silver bullet as you have the same problem for stored procs that use dynamic sql, but at least that's the only hurdle you'll have to get over for Upgrade Advisor.
I don't pretend to be a developer, but I tend to agree with Old Hand that "For trivial inserts and deletes, there can be an argument for avoiding stored procedures". Even then I think I would lean toward using stored procedures so I could have more control over column defaults, etc. Or could the application code handle those as well?
July 30, 2008 at 5:16 am
I prefer the simple queries (just an INSERT, UPDATE, DELETE) to be done directly from the application. But when there's a lot of queries to do at the same time I prefer to do it using Stored Procedure specially when it's done from several places (web, appl, ...).
About performance, no one has spoken about the time lost in the communication between the server and the client computer. The simple test is to make an insert inside a loop. The results differ if they're done in a Stored Procedure or in the application.
If the connection is slow (I've places with that) it's much better to run all the queries in the SP. In fact, now I've some parts that I should move from my .NET application to a stored procedure if I don't want that users get angry...
Josep.
July 30, 2008 at 6:12 am
We use stored procedures for several reasons. first, we don't have to give insert/update/delete access on tables to users. Yes, users can run the stroed procs from Access or other application, but they at least have to take the time to figure out what teh procedure names are, what teh schema is, etc, and we don't give them access to the information_schema views or system that would assist that effort. Many of our SP's are wriiten in a manner that stops execution if the calling application is not the expected app, or a parameter that's in the calling program isn't passed.
Second, we can fix erros more quickly since we only have to change code on a couple of servers rahter than 50,000 desktops. We can change a stored procedure in a week under normal change control prcedures, less if it's a critical item. Changing an executable file takes at least 3 months, sometimes longer, due to the effort involved in generating an install package, testing the installation, regression testing to ensure no other apps are impacted, etc. If the progrma runs on a terminal server, there's another process to go through.
There are occasions we use embedded SQL, such as in a service program that runs on a server rather than on a desktop. Any changes required are limited to a few servers.
July 30, 2008 at 6:23 am
Nice troll bait... I'm a little hungry...
Anyone advocating stance within the development and technicals sphere that says ALWAYS or NEVER is usually wrong. So, saying "ALWAYS use stored procedures" is a good way to demonstrate ignorance. The flip side is this little screed saying "NEVER use stored procedures." Guys and Gals, you do what works to get through the day the best way you can. If that means using stored procedures because of actual benefits that they do offer (more on that in a bit) then you should use them, not follow some dogmatic dictate that shrieks their evil because they don't fit some preconceived paradigm. On the other hand, a very large percentage of TSQL code, for example, the basic three CUD statements needed for most tables, doesn't really have much in the way of benefits by being in stored procedures (assuming it's only doing the work of create, update, delete and other set based operations are not occurring). Do the right thing for the right reasons, not because some artificial paradigm imposed from without says to do it.
Stored procedures are an industry best practice. Note the key word and trick phrase, best practice. They're not a law or an absolute or a THOU SHALT...
All that said, you've got some serious flaws in your argument:
If you read this you will understand that only the part of the query that does not change is precompiled and if the parameters change performance may suffer.
And it may not. In fact, it may, and more often than not, does, benefit from having a reusable execution plan, one that doesn't vary based on the length or quantity of the parameters passed.
SQL Server stores execution plans for any query sent to it, therefore there is no advantage in this department.
Utterly wrong. Read this little entry[/url] on my blog about nHibernate and how it passes parameters. You don't have to change much for the execution plan to be regenerated because of a miss while searching the cache. Just the simple fact of defining your parameters to match your data blows this theory out of the water. Yes, you could make sure that you're passing your parameters appropriately, but now you're starting to write well structured TSQL code using parameters. The step between this and stored procedures is simply one of storage, not structure or purpose.
If any performance difference is negligible.
Demonstrably false. Recompiles and new compiles in a highly volatile OTLP system are a serious bottleneck. If the stored procedures we're talking about here are stupid simple, one table SELECT statements, you have an argument. Everyone that has a relational database where many of the queries are against a single table or where there are no complex joins, please raise your hand....
Also if you are dynamically generating SQL in your sproc and executing it, you will also have no performance advantage.
First absolutely true thing you've said. That's why most of us argue over and over again against using dynamic SQL.
Sprocs also give developers a container to write cursors which are a bad practice (in SQL Server) since they create temp tables.
And giving developers .NET enables them to write Service Oriented Architecture.... your point?
Additionally the most common stored procedures are usually the most trivial SQL statements (insert, update, delete).
Here again, we have agreement. If you want to talk about MOST simple procedures or TRIVIAL procedures and how they don't need to be procedures at all, I'll support you. You're talking ALL because of SOME. Sorry, that's silly.
As to Security, you have some valid points. It really does come down to how secure do you want your data. Some systems really don't have to worry much, as you state. On the other hand, I'd like to think that my bank is putting every layer of security between my money and a bunch of script kiddies as possible. BTW, I do think you may have gotten this backwards... Constructing a string is what exposed the Oklahoma Prison System database to complete exposure, not using parameterized queries. Actually, if you're going to use dynamic SQL, using parameterized queries, where you, the developer, can ensure the data types and commands issues, is absolutely the right way to go. I do want to note that you are effectively granting us that stored procedures are, in fact, more secure.
Reuse, seperation of responsibilities... eh. I don't see these as major arguments. You can have these points for all the good they do.
Stored Procedures are not object oriented... Neither are databases. Shouldn't you just stop using them and go to file storage? Seriously. Why bother with all this mess, normalization, indexes, constraints... They just slow down development and the app can take care of it all, right? But, oh, you want to take advantage of some of the multi-user aspects that databases hand you, right. So you'll keep them in place just so you don't have to worry about concurrency on your XML files. But then, concurrency will be a HUGE problem when you've got recompiles going on constantly, really poor execution plans that aren't taking advantage of indexes, no method for refactoring data access that doesn't require application deployments (which include financial testing and all kinds of other stuff)...
In short, unless the storage mechanisms change, completely eliminating stored procedures is just flat out stupid. Sorry, but there it is.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 30, 2008 at 6:38 am
It is far, far easier to make a change in a stored proc on the database than to change SQL code residing in the bowels of some application written long-ago.
I've learned over the years that there are times when the "rules" can and should be broken. This applies in writing in the english language, it applies in how we handle our kids and it applies in how we manage/access databases. If the new "rule" is to never use sprocs...I'll eventually find times when sprocs are still the best way to go. I've even found times when a fast-forward-only cursor is the best tool for the job (like assigning a unique random number to each of several million customer account records for random sampling for a marketing test).
I'll use the best tool in my toolbox for the job. If that violates some purists' idea of "best practices", so be it.
July 30, 2008 at 6:39 am
Much constructive feedback. I'll try to respond to the various responses:
Remember stored procedures do nothing to protect you from SQL injections, just try executing this code: EXEC GetCustomers; DROP TABLE Customer;
The protection is how we execute sprocs from our code using a parameter collection with the command object in .Net and the like.
One thing to consider as well is that sprocs are vendor specific and require rewrites if you move from your current database platform, while there may be less changes if you moved Sybase to SQL Server, the affects would be much larger moving from Sybase/SQL Server to Oracle.
Most large ERP and CRM systems I have encountered do not use stored procedures and the next generation MVC frameworks are based on some object relational mapping, so the use of this practice may change in our environments with or without or consent.
If you have around 50 tables in a system and you have just 4 sprocs for each (create, update, delete & select) you would have at minimum 200 sprocs. If you have to change a primary key from INT to BIGINT after you make the corresponding changes in your tables you have to make changes in each sproc which may number in the dozens. With ORM you would simply change this in the mapper. To me at least this makes a very good case for ORM, rather than putting faith in each developer to write consistent, quality SQL code you put the onus on the years of refactoring and evolution of an ORM framework.
July 30, 2008 at 6:46 am
I like stored procedure especially I wrote a lot of stored procedures which dynamically did different based on the parameters. For example I wrote a procedure that automatically bcp different text files with different format to the corresponding tables. This could not be achieved by SQL itself. There is a lot more we can do using stored procedure than plain SQL statements.
July 30, 2008 at 6:49 am
:yawn:
Can someone please wake me in 15 years when this all comes to pass? I'm STILL waiting for XML to rock my world.
James Stover, McDBA
July 30, 2008 at 6:51 am
I am something of a newbie but ...
not to use stored procedures? that's nuts
it makes no sense in a networked environment to send over the network a lot of data, then on the client decide how much of it is necessary
otherwise, the app would build this gnarly query, send it over to the database, bring back stuff then massage it into shape before binding it to a control of some sort
did I mention speed of development? ease of testing?
right click to Execute Stored Procedure, fill in parameters, look at result set - get that piece out of the way
then there's the whole reusability thing
even if the actual stored procedure can't itself be reused (i. e. different table) the logic can - and it is in a handy place to look for an example
the one real disadvantage wasn't even mentioned - version control
I've been at a previous site where business logic was buried in stored procedures and when a small change broke a report - all heck broke loose
at my current employer we don't have an official way to keep previous copies of stored procedures (I know - yikes)
I worked on an Oracle application for a number of years
as I recall, data access was done embedded in PRO*C programs
there probably were political reasons (control over logic) and most certainly performance reasons (processing literally millions of transactions each batch)
it was celluar traffic for major European phone companies - you can imagine the volume
July 30, 2008 at 6:55 am
scott.w.white (7/30/2008)
...One thing to consider as well is that sprocs are vendor specific and require rewrites if you move from your current database platform, while there may be less changes if you moved Sybase to SQL Server, the affects would be much larger moving from Sybase/SQL Server to Oracle.
Most large ERP and CRM systems I have encountered do not use stored procedures and the next generation MVC frameworks are based on some object relational mapping, so the use of this practice may change in our environments with or without or consent.
...
Well like thats what a DAL is all about interface and abstraction - two parts - the connection part specific to the DB (SQLServer, Oracle etc.) and the working part which calls the stored procs by name. Keep this second part consistent proc names, params and returned data - you have DB independance in your application.
Hiding under a desk from SSIS Implemenation Work :crazy:
July 30, 2008 at 7:02 am
Scott - to respond to two of your points.
You're right - most large COTS systems do not use Stored Procedures - and it's for one of the reasons you state - portability. It allows a tick in the box for marketing for "Runs on Oracle" and "Runs on SQL Server". Depending on your enviroment this may or may not be relevant in whatever enviroment you are operating in.
Four stored proceures per table. Why? Keep the stored procedures as a funtional interaface- call a proc CreateIncindent, DeleteIncident, whatever, with all the paramaters to update all the underlying tables. One stored proceure per operation - not four per table.
July 30, 2008 at 7:02 am
One thing to consider as well is that sprocs are vendor specific and require rewrites if you move from your current database platform, while there may be less changes if you moved Sybase to SQL Server, the affects would be much larger moving from Sybase/SQL Server to Oracle.
This argument comes up all the time. Small companies might jump from platform to platform, but larger companies tend to entrench their investements. They've been working with SQL Server for eight years at my current company (where I've been for six and a half) and the plans for the next three to five years is to expand SQL Server and continue retiring 30 year old systems written in god knows what as well as getting rid of some Oracle systems. I'm pretty sure they'll continue down that technology path, depending on how the world changes, for another 10-20 years. All on one platform (again, depending on any enormous paradigm shift that no one can predict). Not taking advantage of, for example, a Common Table Expression because it's not supported in DB2 (is it?) seems stupid when you consider that the piece of code might have to live 10 years. Why would I put it into the lowest common denominator at the sacrifice of speed, flexiblity, maintainability and scalability?
This need to immediately jump to another database system just doesn't make any more sense than that you need to write your VB code so that you can immediately shift it to Java (impossible) or your C# code so that it can be immediately shifted to Ruby (ain't happening). Why does this one platform have to be infinitely flexible when the other aspects of the platform are carved in bedrock?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 15 posts - 16 through 30 (of 160 total)
You must be logged in to reply to this topic. Login to reply