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 2021 - 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/
----------------------------------------------------------------------
