SQL in-line code vs stored procedure

[chillay]

Hello there

I have many systems written in VB6. All but one are front ends to a SQL Server 2000 db. The other front ends ACCESS 2000.

I have a ton of SQL statements in the code (albeit, simple statements). Most of the statements are SELECTs. Since I started this course I have been replacing a lot of recordset.addnew code with INSERT statements and a bunch of the EDIT functions with UPDATE statements. Yes, I have been applying what I have been learning. Thanks.

My question... In your learned opinion, which is more efficient; having the SQL statements in-line in the code, or saving them as stored procedures in the db and passing parameters to them at run time?

Bear in mind that I am a one man shop. Support time is always an issue.

 

[DaOtH]

Hi

In case of a bug, issue or whatever it is relatively easy to change the contents of the stored proc as long as the input/output parameters do not change.

It saves you time, because if put it in the in-line code you would need to redistribute the front-end again.

just a thought

"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963

 

[chillay]

Thank you. That makes a lot of sense.

 

[CptCrusty1]

I would agree with the stored Proc. If you get an error, you know its the stored proc and you won't have to dig through your VB. Narrows your search considerably. Also, you're limiting the code you have to write/change to accomodate the SQL in your VB since you'll be writing the SQL in the host language instead of possible going via ODBC, yadda yadda. All said, stored proc, good idea.