Stored Proc vs. Statemnt in VB Code ---- Advantages? 2

[leishad]

VB 6.0, MSSQL 2000

I have just learned how to create stored procedures and how to call them from VB code. I have written a program in VB 6.0 which has many SQL queries. It seems to me that it would clean things up real nice if I created stored procedures and called them from my program instead of using the SQL Statements within my code.

I am not familiar with issues such as resources, speed etc. Is there any reason that I would not want to do this?

 

[SQLSister]

Stored procedures can be faster for many reasons, one of which is that there is usually less network traffic.

ANother reason to use stored procedures is that they make it easier for the dba to manage database changes as they have only one place to look to see what will be affected. Also you can then set security at the sp level instead of the table level making it easier to precidely limit hwat users can do. We do not allow any access to our databases except through stored procedures.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[TJRTech]

SQLSister says:

>> We do not allow any access to our databases except through stored procedures.

Yeah, my recommendations are almost that strong. I prefer to have anything that dirties that database (updates, inserts, deletes) go through SPs, but straight queries can go through SQL Views.

Having ALL queries through SPs removes much of the power of the query engine and T-SQL.

Of course, I have seen shops that do what SQLSister says, and have some pretty sophisticated SPs for queries that take params for the filtering (where criteria) and grouping and ordering, but when you go to that level, why not use SQL views...that's my thought anyway.

Oh, never allow direct table level access. So, to summarize:

Insert, Update, Delete - Through SPs
Selects - Through Views

TJR

 

[leishad]

Thank-you -- this answers even more than my question. I had been concerned about documenting everything I do so that in the future someone could figure everything out. The stored procedures and views(which I hadn't gotten to yet) are clearly the way to alleviate any confusion. Boy, I'm gonna have fun backtracking on my old programming!

 

[TJRTech]

Oh, and Leishad, there is an art, IMHO, to coming up with the design for your views.

I employ a set of conventions as follows for views:

- "vw" prefix for all view names (vwCustomers)

- Plural names for views; singular names for tables (e.g. vwCustomers is a view to the Customer table)

- Table level views for all entity tables (e.g...the very tired vwCustomer view for the Customer table)

- Multiple-related table views for more complex relationships that must join multiple tables (e.g. Customer, Address, AddressType and CustomerAddress tables all broughgt together to make one or more views...vwCustomerHomeAddresses, vwCustomerBusinessAddresses). One last thing that I try to do in such views is return ALL of the columns from each of the various joined tables, thus allowing the programmer to use as few or as many as they need in their query, but avoiding most cases where the programmer would need to join to other views as all related information is in the view...that's the point of such views.


Note how in the last example the complex view really speaks to the programmer in business terms they understand (a list of Customer's Home Addresses), and removes the need to know anything about the data model, what an AddressType is, etc.

It is this abstraction from the data model, and the type tables and relationships inherient within, and the presentment of the information in "business" terms that is most valueable, IMHO.

TJR

 

[leishad]

TJR:
I appreciate the extra tidbits -- I have not had a chance to look into using views yet but previously when you said:

"sophisticated SPs for queries that take params for the filtering (where criteria) and grouping and ordering, but when you go to that level, why not use SQL views...that's my thought anyway."

It sounds like you are saying that views can receive parameters just like an SP and that is what you would choose. Based on the fact that I am the only programmer and database person here my take on it is that it would be of the most benefit to use the SP when parameters are involved because I don't have to essentially repeat the query in my VB APP. Does that make sense to you?

 

[TJRTech]

Leishad asked:

"It sounds like you are saying that views can receive parameters just like an SP and that is what you would choose."

No, I didn't mean to say that.

A view is used just like a table. You use a SELECT statement to select any number of columns of data from the view, optionally using a WHERE clause to filter on certain column values, and optionally a GROUP BY and aggregate functions (like SUM, MIN, MAX) to aggregate.

Since they are just like tables in all regards, you can join views if needed.

The nice part about views is that you can join them and you can construct your own select statement, your own WHERE clause, etc giving the programmer a true ad-hoc and relational ability to query the information they way THEY want it; but at the same time shield them from your table structures and having to know the details of their relationships...which is a good thing, especially if you are a dba and your data model evolves (which all do).

TJR

 

[leishad]

Got it --- really appreciate your info.