Is there a DBA out there who can give a lowly programmer any tips on when it
is better to use a Stored Procedure over an Ad Hoc Query or a Defined View.
At our corporate HQ it seems to be a TABOO to even mention stored procedures
and stored queries to the DBA's; however, everything I read seems to point to
staying away from the in code ad hocs and to the Stored Procedures and
Queries into the Database itself.

Does anyone have any insight on this?

Thanks in advance.

See if this helps:

http://www.sql-server-performance.com/stored_procedures.asp


AMB

Show quoteHide quote

Erland has a great article on his site about using dynamic SQL and stored
procedures.  It should provide you what you are looking for.

http://www.sommarskog.se/dynamic_sql.html

Show quoteHide quote

Hi there in HSV - the happening place...

Well, it is a continues fight and I feel your pain. It happened to me many a
time. The DBA's don't want to loose their control. As soon as you say Stored
proc, the question is who is going to write it? the lowly programmer (LP)?
oh, boy, LP needs permissions now. Does the LP follow the naming convention
the DBA's have? Does LP really know what a database is?... it'll go on for
ever. Then, comes - how about three instances, DEV, TEST, & PRODUCTION. And
let LP have access to DEV and TEST... then again the problems don't go
away... because LP doesn't understand database, you see :) ... all kinds of
stuff.

In programming point of view, you'll be lot better off with SPs & Queries in
the database itself for many reasons... just a few

1) Procedure calls are quick and efficient. Lowers memory requirements and
invocation overhead.
2) Minimizes the use of slow networks, reduces network traffic, and improves
round-trip response time.
3) Updating SPs/View can automatically update your inerface (GUI) with out
you re-installing products (example: Adding a new row to your list view, or
moving your columns around in a listview)
4) Imporved Scalability
5) Better control on Security (without oodles of code)

Cheers,
JP (Just a programmer:))
----------------

Show quoteHide quote

Trigger not executing stored proc ...
Redundant Power Supply
BCP question
Information about an SQL server
Table name as a variable