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 |
|
t1g312
Posting Yak Master
148 Posts |
 Posted - 2003-12-17 : 06:25:03
|
| Hi all,I am working on a VB + SQL Server project. Is it better to use stored procedures or inline sql in VB? Or are there instances when one is better over the other. Can someone give me examples?TIAAdi |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-12-17 : 06:31:58
|
| Stored procedures always.Gives better performance, maintainability, ease of optimisation, troubleshooting, development, ...Make all access to the database via SPs and create a data access layer for the VB code so that you can easiliy turn tracing on and off and also control the database access methods used.The only time you might want to use embedded sql is if you are trying to write an app which is database independent - but that is quite a tricky task.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2003-12-17 : 07:39:52
|
quote:
Make all access to the database via SPs
Even for something as simple as sending a select query to see if a given value exists or not?quote:
and create a data access layer for the VB code so that you can easiliy turn tracing on and off and also control the database access methods used.
Do you have any sample code? What exactly is tracing? That brings me to my next question. I need to keep track of which user has made what change. Is it sensible to create a table to store this information or is there a sql log file that I can use to display this information to anyone who needs it (preferably through VB). |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-12-17 : 07:54:23
|
| >> Even for something as simple as sending a select query to see if a given value exists or not?Yes - it means that if the database structure changes (e.g. for performance) then you don't have to change the application just the stored procedures.Database access layerHere's one.http://www.nigelrivett.net/VB6DataAccessLayer.htmlI need to keep track of which user has made what change.Do you mean an audit trail?seehttp://www.nigelrivett.net/Triggers_2_Creating_Audit_Trails.htmlFor changes to tables.You may want to do this from VB and log changes to entities rather than tables. Think about transaction control though.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2003-12-18 : 01:49:18
|
quote:
Originally posted by nr
>> Even for something as simple as sending a select query to see if a given value exists or not?Yes - it means that if the database structure changes (e.g. for performance) then you don't have to change the application just the stored procedures.Database access layerHere's one.http://www.nigelrivett.net/VB6DataAccessLayer.htmlI need to keep track of which user has made what change.Do you mean an audit trail?seehttp://www.nigelrivett.net/Triggers_2_Creating_Audit_Trails.htmlFor changes to tables.You may want to do this from VB and log changes to entities rather than tables. Think about transaction control though.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Thanks man! Really appreciate it!/me sux @sql server |
|
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2003-12-23 : 01:14:34
|
| Hi nr,Your site is as good as giving away Linux for free!Thanks again!Adi-------------------------/me sux @sql server |
|
|
|
|
|
|
Stored Procedures vs Inline/Embedded SQL