none
Should I ever use inline sql in an application?

    Question

  • I've always taken the approach of creating a stored procedure whenever I'm dealing with a database. But sometimes I wonder if it's overkill to create a stored procedure when the query is a simple SELECT of a table.

    So my question is: should I always create a stored procedure when I need to communicate with a database or are there exceptions to using inline sql?

    Thanks.


    VM

    Wednesday, November 06, 2013 8:32 PM

Answers

  • Yes it makes sense.   It give a layer of insulation between the application and the physical schema of the database.

    It allows the DBA or Database Developer to modify the database for any reason, as long as the stored proc returns the same values. Otherwise, you would need to change your application and redistribute it when the database schema changes.

    Wednesday, November 06, 2013 9:21 PM
    Moderator
  • The advantages of the stored procedure are great, you can make a modification to the stored procedure and not worry about re-compiling your code. Also the stored procedure is compiled in SQL and results in less CPU hits to the server, since the execution plan is already done.

    Chuck

    Wednesday, November 06, 2013 8:38 PM

All replies

  • The advantages of the stored procedure are great, you can make a modification to the stored procedure and not worry about re-compiling your code. Also the stored procedure is compiled in SQL and results in less CPU hits to the server, since the execution plan is already done.

    Chuck

    Wednesday, November 06, 2013 8:38 PM
  • I understand that, but does it make sense to have several simple stored procedures that look like the following?

    CREATE PROCEDURE dbo.usp_GetContactFirstName 
    @LastName varchar(20)
    AS
    BEGIN
    
    SELECT FirstName FROM Person.Contact where LastName = @LastName
    
    END
    


    VM

    Wednesday, November 06, 2013 8:54 PM
  • May be for code re-usability, someone created that.
    Wednesday, November 06, 2013 8:58 PM
  • If that is the case, just use the SQL in your program. I'll do the SQL directly every now and then, when you know that you don't want to change the query and the query is simple.  


    Chuck

    Wednesday, November 06, 2013 9:00 PM
  • Yes it makes sense.   It give a layer of insulation between the application and the physical schema of the database.

    It allows the DBA or Database Developer to modify the database for any reason, as long as the stored proc returns the same values. Otherwise, you would need to change your application and redistribute it when the database schema changes.

    Wednesday, November 06, 2013 9:21 PM
    Moderator
  • Inline SQL is not a preferred option and should only be used if nothing else is available. Not sure what the front end tool you are using to build your application. If you are using .NET then you can use the Entity framework to avoid inline SQL as well as writing stored procedures for simple DMLs.

    Nirmalya Naha

    Wednesday, November 06, 2013 9:32 PM
  • The goal of a stored procedure is to be one statement with zero or more parameters. This applies any repeated action, so one shot SQL is done in-line instead.  This is a gross generalization, of course. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, November 06, 2013 11:20 PM
  • The inline SQL code may require more changes (builds) during maintenance.

    Also, it may create multiple Adhoc plans as the query is not parametorized.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, November 08, 2013 2:57 PM
  • SQL stored in the application might be parameterized, just as SQL in a stored procedure might not be.

    IMO this is something you need to evaluate on a case-by-case basis.  There are pros and cons to both.  

    Using stored procedures is extra work, and requires the use of different tools and different skills.  These are real costs that sometimes outweigh the benefits of using stored procedures.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, November 08, 2013 4:50 PM
  • I believe it makes sense. Your example stored proc accepts a parameter and returns a single column as a data set. Putting this single SELECT in a stored proc helps in reuse of the execution plan, insulates the application from the schema changes, centralizes the access to the table thus making maintenance easier, helps in managing security ... and more benefits.

    - Aalam | (Blog)

    Saturday, November 09, 2013 12:24 AM
  • There are many people who blindly says "use stored procedures" with arguments that do not always hold. My take is that, yes, you can use inline SQL in your application, and with a proper usage - parameterised SQL statements, always using two-part notations - your application design can be as sound as if you use stored procedures. But I also think that you should be consistent. There is a certain advantage if the application does not have stored procedures at all, because it makes deployment simpler. But if you mix stored procedures and inline, you make things more difficult. Maybe the most important point is traceability. That is, the question "where is this table/column used?" This is always easier to answer if you only use stored procedures, but with good isolation in client code it can work decently.

    So my answer is that if you use stored procedures in your application already, stick to that. The only execption are administrative batches like "IF @@trancount > ROLLBACK TRANSACTION" or SET statements when you connect.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, November 09, 2013 9:46 AM
  • So my question is: should I always create a stored procedure when I need to communicate with a database or are there exceptions to using inline sql?

    It depends.  If security is paramount, such as with database containing NPI data, I suggest no in-line SQL and grant execute permissions only on stored procedures.  OTOH, stored procedures provide little value if all queries are trivial and the schema never changes.  Parameterized inline SQL is a viable option in such cases.

    Personally, I use stored procedures consistently to provide:

    • abstraction
    • well-defined database interface
    • performance
    • security


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, November 09, 2013 1:59 PM
  • >I wonder if it's overkill to create a stored procedure when the query is a simple SELECT of a table.

    You should use a view in that case not a stored procedure. Inline table-valued UDF if needed ("parameterized view": http://technet.microsoft.com/en-us/library/aa175924(v=sql.80).aspx).

    Views, UDF-s & stored procedures are server-side objects.

    Inline sql in client app program is not an object just a line of code "somewhere".


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012





    Saturday, November 09, 2013 2:00 PM
    Moderator