faqts : Computers : Databases : Microsoft SQL Server

+ Search
Add Entry AlertManage Folder Edit Entry Add page to http://del.icio.us/
Did You Find This Entry Useful?

8 of 11 people (73%) answered Yes
Recently 7 of 10 people (70%) answered Yes

Entry

Database: SQL: Stored procedure: Should you put SQL in source code or store in stored procedure?

Apr 17th, 2008 23:35
Taksh Verdhan, Knud van Eeden, Deepak Bansal, Rachit Bagda,


----------------------------------------------------------------------
--- Knud van Eeden --- 01 January 2004 - 11:44 pm --------------------
Database: SQL: Stored procedure: Should you put SQL in source code or 
store in stored procedure?
---
---
It is recommended to put your SQL statements (even one liners) in
stored procedures.
---
This because of for example:
---
-Security: -Using the .NET data classes with stored procedures protects
            you against certain forms of hacking.
           -Because you can write your stored procedure such that it is
            the only way and central point to access a certain table,
            you can quite easy manage if certain users have access or
            not.
---
-Separation: Using stored procedures to contain your SQL
             keeps the SQL separate from your source code.
 -Independence: In general it is better to keep this
  basically different information separated, as you can
  change its content without directly influencing and or
  having to change the other parts.
 -Easier to read: if you separate, it reduces the
  amount of information put together, and might
  make your source code easier to read
 -Trouble shooting: having all code in one central
  point only might make it easy to pinpoint the
  problem, and have it solved then for the whole
  interrelated system at once.
 -Reuse: because it is stored centrally, you can reuse
  it in other parts of your program
 -Change only once: if you have to change something
  in your stored procedure, you only have to do
  it in one place.
  So if you put this all together it comes down to creating
  and storing your stored procedures in the database itself
  (e.g. in Microsoft SQL server).
  When the SQL procedure is stored in the database, it does not have to
  be replicated in each client.
  ---
  This saves programming effort especially when different client user
  interfaces and development systems are used.
  For example, suppose you write your software to access your database
  in ASP, PHP, JSP and ColdFusion and some local C++ GUI interface that
  runs on your local desktop. If this programs all need to run the same
  SQL query, then if you have that query source code stored in one
  place, thus here in the database (e.g. in your Microsoft SQL Server),
  then instead of having to change in all that programs that query
  source code, you only have to change it once in the database.
  This saves thus much programming effort especially when different
  client user interfaces and development systems are used.
  Thus also the principle
  'change your data once, and use your data everywhere'.
---
-Speed: The processing of your stored procedures takes place with
        also the aim to increase the speed of this processing.
        If your stored procedure is called the first time,
        some optimization takes place.
        In subsequent calls of this stored procedure
        this optimized form is used.
        In general you can expect up to say 25% faster SQL code
        processing.
        -Compilation: your stored procedure is compiled
                      instead of interpreted
---
---
Book: see also:
---
[book: author: Greenspan, Jay / Bulger, Brad - MySql/PHP Database 
Applications - ISBN 0-7645-3537-4 - p. 19 'Stored procedure']
---
[book: author: Payne, Chris - title: Teach yourself ASP.NET in 21 
days - ISBN 0-672-32445-8 - p. 423 'Stored procedures']
---
[book: author: Shepker, Matthew - title: Writing Stored Procedures for 
Microsoft SQL Server - year: 2000 - ISBN: 0-672-31886-5]
---
---
Internet: see also:
---
[Internet: Book: Wrox: Beginning ASP.NET 1.1 with VB .NET 2003 - 
http://www.wrox.com/books/sample-chapters/samplechapter_0764557076.pdf]
---
Database: SQL: Query: Stored procedure: Can you give an overview of a 
stored procedure?
http://www.faqts.com/knowledge_base/view.phtml/aid/27892/fid/147
http://www.zecco.in/
http://www.newsvine.in/
http://www.freecodereference.com
http://www.autoaccessoryshipping.com
http://www.autopartsdirectory.info
http://www.cssbunch.com
http://www.freesharewarez.info
http://www.freewebsitesubmission.info
http://www.gamecheatsdome.com
http://www.gamesgeeks.com
http://www.metaldetectorprices.info
http://www.onlinenewsletterarticles.com
http://www.reversecellphonedirectory7.info
http://www.webcodirectory.com
http://www.webdesignsmagazine.com
http://www.wordpressthemesbox.com
http://www.templateremix.com
http://www.websitedevelopmentcompany7.com/
http://www.carautoaccessories7.com/
http://www.beautysupply7.com/
http://www.templatemixture.com/
http://www.rajasthantour7.com/
http://setmax.co.cc/
http://www.seoservicesinc.com/
http://automotivedir.mylinea.com/
http://allwebdirectory.mylinea.com/
http://healthdir.mylinea.com/
http://convonix.blogspot.com/
http://www.autoaccessoryshipping.com/auto-accessories/
http://www.carautoaccessories7.com/car-accessories/
http://www.templateremix.com/free-powerpoint-templates/
----------------------------------------------------------------------