What are the pros and cons to keeping SQL in Stored Procs versus Code [closed]

by

I am not a fan of stored procedures

Stored Procedures are MORE maintainable because:
* You don’t have to recompile your C# app whenever you want to change some SQL

You’ll end up recompiling it anyway when datatypes change, or you want to return an extra column, or whatever. The number of times you can ‘transparently’ change the SQL out from underneath your app is pretty small on the whole

  • You end up reusing SQL code.

Programming languages, C# included, have this amazing thing, called a function. It means you can invoke the same block of code from multiple places! Amazing! You can then put the re-usable SQL code inside one of these, or if you want to get really high tech, you can use a library which does it for you. I believe they’re called Object Relational Mappers, and are pretty common these days.

Code repetition is the worst thing you can do when you’re trying to build a maintainable application!

Agreed, which is why storedprocs are a bad thing. It’s much easier to refactor and decompose (break into smaller parts) code into functions than SQL into… blocks of SQL?

You have 4 webservers and a bunch of windows apps which use the same SQL code Now you realized there is a small problem with the SQl code so do you rather…… change the proc in 1 place or push the code to all the webservers, reinstall all the desktop apps(clickonce might help) on all the windows boxes

Why are your windows apps connecting directly to a central database? That seems like a HUGE security hole right there, and bottleneck as it rules out server-side caching. Shouldn’t they be connecting via a web service or similar to your web servers?

So, push 1 new sproc, or 4 new webservers?

In this case it is easier to push one new sproc, but in my experience, 95% of ‘pushed changes’ affect the code and not the database. If you’re pushing 20 things to the webservers that month, and 1 to the database, you hardly lose much if you instead push 21 things to the webservers, and zero to the database.

More easily code reviewed.

Can you explain how? I don’t get this. Particularly seeing as the sprocs probably aren’t in source control, and therefore can’t be accessed via web-based SCM browsers and so on.

More cons:

Storedprocs live in the database, which appears to the outside world as a black box. Simple things like wanting to put them in source control becomes a nightmare.

There’s also the issue of sheer effort. It might make sense to break everything down into a million tiers if you’re trying to justify to your CEO why it just cost them 7 million dollars to build some forums, but otherwise creating a storedproc for every little thing is just extra donkeywork for no benefit.

More Related Contents:

  1. Passing List to SQL Stored Procedure
  2. What is passing parameters to SQL and why do I need it?
  3. Add WHERE clauses to SQL dynamically / programmatically
  4. How to pass a null variable to a SQL Stored Procedure from C#.net code
  5. Delete from two tables? [closed]
  6. Procedure or function xxxxx has too many arguments specified [duplicate]
  7. An unhandled exception of type ‘System.InvalidOperationException’ occurred in System.Data.dll?
  8. How to execute a stored procedure within C# program
  9. Call a stored procedure with parameter in c#
  10. Using stored procedure output parameters in C#
  11. Insert entire DataTable into database at once instead of row by row?
  12. Getting return value from stored procedure in C#
  13. SQL error: Incorrect syntax near the keyword ‘User’
  14. Query extremely slow in code but fast in SSMS
  15. How to retrieve data from a SQL Server database in C#?
  16. Using DateTime in a SqlParameter for Stored Procedure, format error
  17. Code to validate SQL Scripts
  18. Any way to SQLBulkCopy “insert or update if exists”?
  19. SQLException : String or binary data would be truncated
  20. Multi threading C# application with SQL Server database calls
  21. ALTER TABLE DROP COLUMN failed because one or more objects access this column
  22. How to pass XML from C# to a stored procedure in SQL Server 2008?
  23. When executing a stored procedure, what is the benefit of using CommandType.StoredProcedure versus using CommandType.Text?
  24. Can I use ADFS 2.0 to authenticate certain users against SQL Server?
  25. ExecuteNonQuery returning -1 when using sql COUNT despite the query string
  26. How to insert data into SQL Server
  27. Use SQL Server time datatype in C#.NET application?
  28. Generating sql code programmatically
  29. How to give ADO.NET Parameters
  30. Is SqlConnection.Close() need inside a Using statement?

Leave a Comment