* named parameters
* prepared statements
* code can be tested via db tools

Snippetory SQL - prepared statements made simple

When dealing with databases it's often necessary to deal with complex statements. As such complex statements tend to be expensive it often makes sense to be able to optimize them often, to gain best performance with a growing dataset. Optimizations can be done in the UI of the datadase, but there's a problem to gain the right statement from your code, and afterwards getting the changes back to code. That's where intact templates come handy.

-- $variables{ set @currency = 'EUR'; set @catId = 17; -- }$ SELECT products.* FROM products LEFT OUTER JOIN prices ON ( products.id = prices.productId AND prices.currency = @currency ) WHERE products.id IN ( SELECT prodCat.product.id FROM prodCat WHERE prodCat.categoryId IN (@catIds/*delimiter=', '*/) )

This makes it very simple to copy the template back and forth. Having the variables in a region makes sure, they don't end up in the output. The comment coating hides the template markup from the SQL tooling. And everyone is happy.

Complex statements tend to require variants. Maybe not all search fields are fields, maybe users have different access rights. Lots of different requirements can be implemented via variants. And building stuff with complex logic is exactly what Snippetory was build for.

SELECT * FROM tblTest1 WHERE value = :mandatory /*${*/ OR value = :opt1/*}$*/ /*${*/ OR value = :opt2/*}$*/
Bernd Ebertz Head, Founder and chief technology evangelist of
jproggy.org