msmeth

Hi everyone. I have used previously created stored procedures, just calling them from my VB6 applications, but I've never really written one. Well, I wrote one, but it wasn't that intricate. Now I have to write some more and I've been looking on the web for tips and all that and I came across some sites that said stored procedures are bad. Can you guys shed some light on this for me? I was under the impression that they're more efficient than dynamic SQL queries, but now I'm confused....

Harkon

to my knowledge they are 'good things' too.
can you place some reasons or link as for why they are bad?

msmeth

This is one: http://weblogs.asp.net/fbouma/archiv.../18/38178.aspx

Merrion

If you _only_ have one application connecting to the database and have _total_ control over changes to it then it is OK to use on the fly sql generation - but in what we in the real world find is that stored procs are good.

HardCode

I stopped reading his article after he stated, "...the stress of maintaining a lot of stored procedures, to write another stored procedure for each thing you want to do, is gone. Dynamic SQL is the future. (Dynamic SQL is generated on the fly by a generic piece of code which gets various data as input and generates a parametrized query from it."

Anyone savvy enough with Transact-SQL can do the same thing with a stored procedure. I hate nothing more than to concatenate a 9349839 character long string to form the SQL to run, with all of the fun ' " & sMyName & "');" etc, etc.

Maybe he should read about SQL Injection attacks before he bashes stored procedures?

*In my best Richard Dawson voice* Survey saysssssssssss *BING* SPs = GOOD

loquin

Allowing access to updating the data through a stored procedure allows you to encapsulate the database, and to make it more "object oriented." In effect, the SPs are your exposed methods.

In addition, you can control access to the tables by the SP's themselves, as they may be granted table access, where the user would not.

SP's also provide a convenient means to encapsulate various business rules that would be too abstract to enforce via relational integrity.

Finally, SP's DO reduce network traffic. In a busy network environment, with potentially hundreds or even thousands of clients, you want to avoid the possibility of upgrading network speeds to support a database application when it scales up. Your CIO would frown on you if you created an app that proved to be a necessity, but you had to install gigabit network hardware just to support the full number of clients needed because of ineffecient traffic requirements.

msmeth

Okay I'm glad to see all these responses. Now I can continue in the way I wanted to do this application

Can I ask you guys too, if it's best to use SPs for this or should I use dynamic queries. The application is basically a giant data entry thing, with the users entering data into a 24 column grid. They don't always enter something into every column. Should I use a stored procedure for the inserts?

loquin

That depends.

How many clients are you looking at in the fully deployed system? If more than a 10-20, SP's would be nice.

How many joined tables are targeted? If more than one table is joined, then SPs are nice; you pass the data set; it takes care of updating all the related tables within a transaction & returns success or failure by raising an error.

Should the table be secure from clients directly accessing it? If so, SP's are a necessity.

Are there many business rules, and/or are these rules subject to change? If yes, encapsulating the rules in stored procedures is a convenient way to implement the changes without re-distributing client apps.

msmeth

1. There shouldn't be more than 10 clients.
2. Only two joined tables are targeted.
3. I'd like it to be secure from direct access.
4. What do you mean by business rules?

What about stuff like data verification (like temperature should be a valid number)? Should I take care of that kinda thing in the SP or in my VB code?

loquin

Then all the other points are moot. You should use stored procedures to update the tables, and views to retrieve the data.

Well, maybe one would be If Employee Type = 'Exempt' then Salary is fixed, else overtime pay rules apply. You could either put the rules in your application, which would mean that if the rule ever changed, your application would have to be updated and re-distributed, or, you could code the rule into the stored procedure(s) for adding or altering an employee record. Then, if/when the rule ever changed, you would simply change it in the SP, and all the client apps would "keep on ticking."

msmeth

Okay cool. I don't think there are any business rules, but I haven't really looked at the data yet. I highly doubt it though. Thanks a lot for the insight. I don't know how to use views so expect a post or two from me about those

msmeth

Alright I'm working with views now. Basically, can you tell me what the benefits of them are? The tables I'm using don't join or anything.

And, how do I execute a view from VB6?

MKoslof

You started another thread regarding the benefits of view, please refer to that thread .

Regarding selecting records, you should be able to do it, just as you would a table. You can run a SELECT or UPDATE against the view as it was a table. A view is another data object within your sql server. The same would apply with stored procedures..you can reference the view as you would a table object.