StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If its a nightmare to debug, then you already have your answer: its not a best practice, and the pattern should be avoided in the future.</p> <p>For what its worth, dynamic SQL is not necessarily a bad thing, and its extremely common in large business applications. When its done right, it can improve the maintainability of code:</p> <ul> <li><p>Most ORMs like Hibernate make database access transparent to the programmer. For example, when you request an object like <code>User.GetByID(12)</code>, the ORM will dynamically construct the SQL, execute it, map the fields to your object, and return it. As a programmer, a huge amount of your time is freed up because you don't need to write SQL queries anymore, you can just focus on writing your application. <strong>You can query the database without seeing, touching, or smelling a hardcoded SQL string anywhere in your application, so that you maintain a hard separation between business logic and your data access layer without writing stored procedures.</strong></p></li> <li><p>If your dynamically constructed SQL is well-abstracted, then you can swap out databases without changing your data access logic. NHibernate, for example, generates correct SQL based on whatever database vendor is listed in your config file, and it simply just works. <strong>You can, in principle, use the same data access logic with any database vendor.</strong></p></li> <li><p>I've seen applications which contain 1000s of stored procedures representing trivial queries (i.e. simple selects, inserts, updates, and deletes). Maintaining these databases is beyond cumbersome, especially when your database schema changes frequently. With well-written dynmamic SQL, you can represent 1000s of equivalent queries in just a few functions (i.e. a function where you pass a tablename and some params). ORMs already provide this abstraction for you, so changes to your database schema do not require changes to your application code (unless you want to add that new field to your business object, of course). <strong>You can change your database schema (add new fields, change datatypes of columns, etc) without changing your application code or data access logic.</strong></p></li> <li><p>There are some smaller benefits as well:</p> <ul> <li>Regardless of whatever whatever policies there are which require programmers or CMs to copy stored procedures into source control, we forget. If SQL is built up programmatically, its always in source control.</li> </ul></li> <li><p>There are lots of myths about the advantages of stored procedures over dynamic SQL:</p> <ul> <li><p>"Dynamic SQL is prone to SQL injection, stored procedures are not" - simply not true. Dynamic SQL is trivial to parameterize, and in fact parameterized dynamic SQL is easier to write than dynamic SQL which uses excessive string concats. Even still, if you work with a programmer who is so negligent that he writes unsafe dynamic SQL, then chances are he'll write stored procedures just as badly (see <a href="https://stackoverflow.com/questions/434414/what-is-the-most-evil-code-you-have-ever-seen-in-a-production-enterprise-environm/434562#434562">this as a case study</a>).</p></li> <li><p>"Stored procedures are pre-compiled, so they run faster. SQL Server can't cache the execution plan of dynamic SQL" - at least with SQL Server, this is absolutely wrong. Since SQL Server 7.0, "<a href="http://msdn.microsoft.com/en-us/library/aa174792.aspx" rel="nofollow noreferrer">a stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.</a>"</p></li> <li><p>"Stored procedures let you change SQL without redeploying an application." - this is true for very trivial tweaks, but in 95% of cases, changes to the database require application changes anyway.</p></li> </ul></li> </ul> <p>Dynamic SQL isn't bad <em>per se</em>, but there are lots of warning signs which indicate when you're doing it wrong:</p> <ul> <li>You should <em>never</em> see regex to dissect an SQL statement.</li> <li>You should <em>never</em> pass chunks of SQL to your data access layer. For example, a method with the signature <code>User GetUser(string whereclause)</code> is very fragile. If your database schema changes, your application logic changes as well.</li> <li>Data access should be typesafe. You should never pass around untyped datasets or strings -- or if you do, you need to wrap these objects in a typed object before returning them to the user.</li> <li>Always consider using an existing ORM before rolling your own.</li> </ul> <p>You've probably noticed I've talked a lot about ORMs in this post. That's because, in general, hard-coding SQL strings is a <em>bad thing</em>. The correct way to use dynamic SQL is to have a tool which generates SQL on-the-fly, which is exactly what many ORMs can and already do.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload