Architect: "Inline sql is just as performant as a stored procedure and since it is in code its safer and easier to maintain."

Me, inside my head: "I bet I could do the pencil trick on him from 'The Dark Knight' and it wouldn't hurt him as much as suck the world into the small hole in the front of his head since it is clearly a vacuum which was meant to destroy the earth. This is an obvious plant by the lizard people as a test to see if we could identify them. Killing him would be a..."

Architect: "I mean isn't it still a best practice."

Me, out loud and deadpan: "No, that is wrong and it was never a best practice. "

Me, inside my head: "Crisis averted."

I didnt quite get this. Are Stored Procedures bad or is inline SQL bad?

Inline sql is bad for a lot of reasons in most major languages. It poses a possible security vulnerability (old PHP was known for this) and it does not take advantage of the rdbms query caching. Even though it does exist and is known to be around in a lot of either code which is currently in development or old insecure code you should avoid it whenever possible. You should use tools such as the Entity Framework, nHibernate or logic stored in the database (stored procs, functions, views...etc.) whenever possible.

Its an owasp top 10 vulnerability: https://owasp.org/index.php/...

It also is indicative if half baked code in a lot of situations.

@missionearth I dont really agree with you. Stored procedures are business logic inside database, a place it does not belong to. Its hard to manage and almost any change will break most of the app which use that db. On the other side, stored procedures for me are only good as a data control measure but thats pretty much it. As RDMS query caching is concerned it is also available for inline sql (but maybe not on the rdbms you are using). And languages got pretty good with handling protection from sql injection (which too can happen even when using stored procedures on some dbs). So IMO, a good query builder is just as good, less fragile and more useful for manipulating data than stored procedures. But I guess you have your use-case and I have mine :)

@arekxv I am fine with your difference of opinion. In my world, Inline sql is bad in a lot of ways. You are correct that query caching does exist when you fire any query against certain rdbms's however, having to recompile code and redeploy it to production makes it difficult in my environments that I work in. Additionally, the security auditors get itchy when they see any dynamic sql written in source. Now, if you use EF or nHibernate you can accomplish the same thing as inline sql with the advantages of having the query logic in your code. I am not saying that there is not a place for inline sql, there is, I just don't think that in the conversation that I was having with this architect that what he was trying to say was relevant to the subject matter we were discussing.