5 ms·
Never ask me to review or support any code that's using dynamic SQL written in MS T-Sql or Oracle. Not that dynamic SQL is inherently bad, but the procedural a
by Pxtl 3y ago
Never ask me to review or support any code that's using dynamic SQL written in MS T-Sql or Oracle. Not that dynamic SQL is inherently bad, but the procedural and string-management tools of those languages are appallingly awful and SQL is a bad tool for templating/smashing strings together.
Which, fundamentally, is what dynamic SQL is.
When I realized the article was going to be about that kind of dynamic SQL, I closed the tab.
Building SQL queries dynamically is fine.
Doing it within the SQL server is an abomination.
- wfriesen 3y agoComing from an Oracle background, both application and data warehousing, I wholeheartedly agree with the articles first paragraph saying that application based dynamic SQL is horrible, but DBA (and I'll include data warehousing) dynamic SQL is great, but it's essentially about how complex the thing is. A DBA is likely doing things like looping over metadata tables and running 'grant select on ' || some_table || ' to ' || some_user While an app developer with free reign will end up doing something much more complex, and much harder to reason about/tune
- nextaccountic 3y agoWhat about some_user has characters that need to be escaped?
- rickboyce 3y agoThe parent article touches on this point too - both Oracle and SQL Server (and any other major db) supports parameterising values in dynamic sql. some_user would become @some_user (SQL Server) or :some_user (Oracle). (The characters never actually get escaped with parameterisation - they are not part of the query text when it is parsed so can’t affect it - hence parameterising a value in sql query replaces the need to escape it with something much more robust.)
- deleted 3y ago[deleted]
- chasil 3y agoIt is also somewhat frustrating that bind variables cannot be used with DDL. I am perfectly able to: SELECT ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME=:var; But I cannot: ALTER USER :var ACCOUNT UNLOCK; I really don't understand why that capability wasn't added a decade ago, as loud as the advice is to avoid hard parsing.
- justinclift 3y agoYeah. This is a definite problem when working with user generated databases (eg untrusted), that could have pretty much anything in them. :/
- marcosdumay 3y agoThat, and the lack of functions with a varying number of parameters, and lack of type polymorphism, and of easy to construct local views, and the lack of sum types... Dynamic SQL is used on practice to fix a lot of problems the SQL people have been refusing to touch.
- mrbungie 3y agoThis, if you want to go the dynamic SQL route, please do it using some decent glue language or templating system that is not SQL itself. Unless you hate yourself that is.
- rickboyce 3y agoFor analytics work tools like dbt are really gaining traction for this reason. Most of the OLAP / warehousing stuff I used to lean on dynamic sql for can be done more cleanly, with the benefit of easy source control. https://www.getdbt.com/ https://www.getdbt.com/
- chubot 3y agoSo this is about creating SQL strings with SQL? Not interpolating SQL with ? placeholders in say Python. The article could have made that much more clear. It reminds me of the bash-completion project which is the default on Debian-based distros. It tries to parse bash IN BASH. This is a very bad idea. Oil Uses Its Parser For History And Completion - https://www.oilshell.org/blog/2020/01/history-and-completion.html https://www.oilshell.org/blog/2020/01/history-and-completion... I would also argue that parsing bash in C is not a great idea either!
- sommar 3y ago[dead]