August 28, 2005 at 10:47 am
Hi im new to sqlserver and database programming and i have a begginers question.
I'm writing a database aplication in delphi and i want to know if its better to write all the sql querys in the server as stored procedures and then calling them from the code (even simple ones)
or
just keep the transactions with only one sql query in the code and send it to the server ...
thank you
August 28, 2005 at 2:09 pm
Most will agree that it is better to use stored procedures and have the application call the procedures. One issue is performance, stored procedures are more efficient than dynamic queries. The next issue is security. You can give the user accounts execute permission on the proc without giving access to the tables. Additionally, using stored procs protects your database from SQL Injection attacks if there is a web front end.
Be sure to avoid names of stored procedures that start with "sp_".
Hope this helps,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
August 30, 2005 at 2:51 am
There are other issuee which are
1. transparancy
2. scalability
3. bussines rule enforcement
with sp you can tackle all of these issues in one time. It's killing 3 birds with one stones.
an example transparancy and scalability is using sp provides an independen physical view to your application. This means you can modified base table like partitioning the table (vertically or horisontally) without a need to change apps.
Scalability example is when you using sp your app doesn't need to know where the real data stored. It means you can have another server to serve query without the need to modified app when a new server installed on network
Bussiness rule example is when you use sp it provides a single gate in to modified data regardless any application which connect to database. Just one sp that will serve any application, save time and money right.
August 30, 2005 at 10:53 am
thank you, you've been a great help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply