SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Your Thoughts On In-line SQL


Your Thoughts On In-line SQL

Author
Message
bkubicek
bkubicek
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9723 Visits: 1092
Comments posted to this topic are about the item Your Thoughts On In-line SQL
Stefan LG
Stefan LG
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1785 Visits: 280
I fall into the "stored procedures" group.
At the moment we are busy with a project to replace all in-line SQL with stored procedures as well as implementing some SQL-injection prevention code.
The slow DBA-scenario is not really an excuse and rather points to some other organisational problem that should have been fixed earlier!
Daffodil
Daffodil
Old Hand
Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)

Group: General Forum Members
Points: 379 Visits: 489
I agree with you 100%. I've been on the front line too many times having to debug an error in production. If you can see the SQL statements in a stored proc then half the battle is won.
andycadley
andycadley
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4560 Visits: 1942
Inline sql basically turns the entire database into a massive number of global variables. If that sounds like a good idea to your developers, you need better developers....
Andy Robertson
Andy Robertson
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3884 Visits: 694

I'm totally for the use of stored procedures as a general rule. However, I can think of the odd argument for the use of inline sql. Sometimes it's easier as a developer to generate sql for particularly complex reports within a reporting application. For example if you have to get a large number of different values based on a large number of different parameters it may be more practical to generate inline sql than 1 or more monstrous procedures that take lots of arguments and maybe end up looking a bit procedural anyway. This might especially be the case if you only want to return a small amount of aggregated data based on complex requirements rather than return a large volume of data that you then aggregate in the reporting client.


Black_Knight
Black_Knight
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2025 Visits: 72
I'm another advocate of the "Stored Procedures" only group, however, pragmatism has a place and there may be specific instances where in-line SQL is the answer, however, those instances should be well documented and only used in extremely rare situations. I agree with Andy regarding complex reports, however, I would still want to check if it would still be better performed in a stored procedure first.
funbi
funbi
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4774 Visits: 1394
For simple CRUD operations I would imagine most developers these days would favour an ORM framework over inline SQL or stored procedures, then use SP's for more complex queries/reports (from what I have seen over the past few years anyway).
n.ryan
n.ryan
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2009 Visits: 422
There are definitely occasions where stored procedures really don't cut it because the logic involved in the query is much better handled in an environment that can deal with the logic better. These are fairly rare and it's usually where the query needs to be altered depending on the inputs provided. While it is possible to have multiple stored procedures and for the application to choose which one to use the number of possible permutations can make this a poor option as can query efficiency options. In any case when I come across a dynamically created query and it is not executed as a paramaterised query I will go postal on the developer - there is no excuse for allowing a route for SQL injection attacks into a system when the fix was developed a couple of decades ago.

An advantage of application defined queries is that all of the application logic is within the same environment and the same version control system. This can be particularly important because version controlling (and just identifying the version of a stored procedure or function) is thoroughly painful in MS-SQL. Expanding version control systems and application deployment systems to include other environments is an important component of the application process but in general is woefully behind requirements without manual intervention added.

On the other hand, as soon as these stored procedures (and udfs) are considered an API then there are very strong arguments to keeping such logic closer to the database where changes to the database structure can be abstracted away from the API to some extent and the efficiency of the stored procedures and functions can be dealt with closer to the source.

I'd tend towards the stored procedure and function route where possible even though the application deployment and version control is considerably harder. Like much of life, and development, there is no 100% rule though.
call.copse
call.copse
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15100 Visits: 2484
I wouldn't advocate in-line SQL, but as funbi says, that's not the real alternative these days. We're using EF and this has many advantages.

Essentially it's a simple engineering trade off. We're often writing simple view / filter / edit pages for entities that will not ever number over 100. We need the pages, they don't get used more than once a week / month / whatever and would only be used by one admin at a time say. You don't need to craft procs for that. Or for filling drop downs, checking a flag, or many other simple application tasks we do all the time.

When a page is used all the time we have a simple decision tree that helps us to decide whether to use EF / paged EF / a simple stored proc / a paged stored proc as appropriate. Yes, if you let an ORM just do what it wants and don't think about it you will get horrible, horrible code and a very slow site (or whatever). With a bit of thought and craft, you can get a really well developed application. Oh and SQL injection is covered, change control is easier than with procs - change a field and the code won't compile, with an error wherever it's used.

But no - don't use in-line SQL :-)
Mike Hinds
Mike Hinds
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3888 Visits: 1235
+1 "Stored Procedures Only".
I am told, but have not done it yet, that parameterized queries may be an acceptable substitute for "Stored Procedures Only".

Mike Hinds
Lead Database Administrator
1st Source Bank
MCP, MCTS
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search