Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
debradeng
Starting Member
46 Posts |
 Posted - 2006-11-02 : 20:44:37
|
| For "select...from...where..."kind T-sql statements,in many cases,we can replace them into SP to reduce the network traffic.when insert or delete or update tables,do we need to replace all of them into SPs?what would happen if an application access database, its queries include both SPs and Dynamic sql statements,is it bad?I think in many cases,we can use SPs to replace Dynamic sql statements which are frequently used.Am I right? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-02 : 21:51:41
|
| It is better to use procedures for many reasons.1. Better performance from query plan caching.2. It is not necessary to grant direct permissions on tables.3. More robust error handling.CODO ERGO SUM |
 |
|
|
debradeng
Starting Member
46 Posts |
Posted - 2006-11-02 : 22:02:13
|
| Thank you,Michael.But is it necessary to replace all of Dynamic-sql into SPs,even inlude the operations as inserting data into tables,deleting data or updating tables,which are not used frequently? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-02 : 22:12:08
|
| It is a very good practice.Obviously, it is not necessary in that you are not forced to.CODO ERGO SUM |
|
|
|
madhuotp
Yak Posting Veteran
78 Posts |
Posted - 2006-11-02 : 23:54:57
|
| Hi,It is always advisable to use SP even you want to do I/U/D. First and foremost reason being the Access Control (permission control) and code re-usability. You may want to insert into same table from many forms(Front-end) and you end up writing code every where. If the schema changes then you have to apply every where. If you implement to logic in sp it has centralised control and you need to change only in one place. For update and delete you have to find the record first to do U/D. so the index is used. so the cached execution plan will speed up your operation in case of SP.Madhu |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-11-03 : 00:11:58
|
| It is safer to assume that dynamic SQL is a weakness in your application. If you can't provide a solid reason for why you are using dynamic SQL, (meaning you've exhausted other methods for accomplishing the same task) then it is a weakness and a vulnerability. Getting code done quickly or easily is not sufficient. You will almost certainly grant too much access to too many users and not have enough security to keep undesirables out. |
|
|
|
debradeng
Starting Member
46 Posts |
Posted - 2006-11-03 : 00:14:12
|
| Thank you madhu,good point! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-03 : 06:44:03
|
My reasons for SProcs over Dynamic would be:1) Centralisation of SQL Code and Business rules2) Security / permissionsYou only get (2) if you have no dynamic SQL anywhere - one little bit and you need to provide (that user at least) with some direct-table permissions.For (1) we like being able to test SProcs in isolation of the application. All our sprocs contain a set of tests (commented out) so that we can easily re-test any new changes - without having to get to Screen 146 of the application  ). Some of our tests wrap the Sproc in a transaction, which is rolled back, to stop it actually updating the tables - also useful:BEGIN TRANSACTIONSELECT * FROM MyTable WHERE MyPK='1234'EXECUTE MySProc @MyPK='1234'SELECT * FROM MyTable WHERE MyPK='1234'ROLLBACKgives me a Before/After view of the row so I can see what has changed.We can also roll-out a change to a single SProc more easily than an Application. An application requires that we roll out a new ASP page, which may have code in it for all sorts of other scenarios, and which might be at odds with the rest of the client's application, whereas we can get the exact code for a single-purpose SProc out of our Version Control system, test a specific change, and apply that to a customer database with much less likelihood of collateral damage.Kristen |
|
|
|
|
|
|
|
|
Use SPs instead of Dynamic sql?