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 |
|
iancuct
Yak Posting Veteran
73 Posts |
 Posted - 2004-07-12 : 14:14:43
|
| Hello, I wanted to know if anyone thinks that there are certain cases when inline SQL is better then a stored procedure. For example if you have VB app and you access either data from an access db or SQL server 2000 should you ever use SQL inside your VB code? Or is it strictly and no questions asked that there are no situations in which SQL inside VB code is good? and therefore we should allways avoid it?? |
|
|
mikejohnson
Posting Yak Master
153 Posts |
Posted - 2004-07-12 : 14:58:45
|
| from what i know, a stored procedure will usually out perform inline code. it's also the more secure solution. however, a lot of times i use sql inline for when i have to generate large sql statements on the fly. |
 |
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-12 : 17:14:10
|
| Well, there are certainly cases where it makes sense. A situation often arises when trying to build a generic framework where you just can't forsee what objects the user will present to you, and so you have to reflect on the object and build up a sql statement from it's metadata. Along the same lines is when you want to give the user ultimate freedom when designing a query (say for a reporting engine or something) and you want to allow a small subset of sql (queries only please) to be entered into a prompt. You don't know what the user will enter so it may make sense to create a dynamic statement. Some databases don't support stored procedures (mysql 4) and therefor give you no option but custom functions or inline sql. In general, using stored procedures is faster (the statements are precompiled) and more secure (they are harder to edit than inline sql, you will use parameters, the authorization logic is centralized). Also, with inline sql (especially sql that is built up dynamically through a complicated process) has a greater chance of being malformed because you don't have a 100% guarantee that you know what you will get until you get it. Sure, a good design will pretty much make this a moot point, but a bad design leaves open the possibility of 100 table joins and all sorts of nonesense that will halt your system. |
|
|
|
iancuct
Yak Posting Veteran
73 Posts |
Posted - 2004-07-13 : 14:35:54
|
| thanks. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-07-13 : 14:58:47
|
| it depends on how you implement it.if you generate sql statemetns throughout your program all over the place, i'd say it's a bad idea. but if you can put everything into 1 class or 1 set of functions, then it's not as bad. you want to keep the data access parts of your code isolated from the business logic for when you (inevitably) change the database structure.the nice thing is, if you do everything through a class or some sort of layer, then you can change whether you use dynamic SQL or stored procs in only 1 place if you decide you need to go in the other direction.- Jeff |
|
|
|
|
|
|
|
|
SQL inside VB code??? is it ok???