Wow... finally a sensible discussion about SP vs inline dynamic. I get soooo
fed up with hearing developers saying how much quicker SP's are compared to
inline sql when in fact in 90%+ of cases the difference is nano-seconds. But
yes there are other advantages which have been very elequently pointed out.
However, there are advantages to dynamic inline sql.
I recently built a framework for an NHS trust where they wanted to be able
to have classes that they specify the tablename and field names that they are
interested in and the framework would generate a class based on those
properties, in this instance you can ONLY achieve this with dynamic inline
sql as the connection string was specified in the constructor of the class
and could be changed at runtime, therefore there is no way that you could use
sp's as you would have to deploy the sp's to the databae before you could use
it.
Also for lookup data, dynamic sql is far, far simpler. In a previous job a
guy we will call JimBob wrote an sp which depending on what type of lookup
you were looking for built up a sql string inside the stored proc to select
the various fields from the various tables, however, it was a nightmare as he
had so many double quotes to worry about that it ended up working brilliantly
until one day someone entered something with 3 single quotes in one of the
fields, which totally fubar'd it all and by the time he had written all the
checking etc into the SP it was totally unmaintainable by anyone but him.
One of the other advantages of SP's over inline is the fact you can create
global temporary tables and use cursors etc, which I'm sure is probably
possible with inline, but it would be very, very messy.
So as you have probably gathered yourself by now, it is a case, as with so
many development theologies of horses for courses, there are some situations
where inline is better, some situations where SP's are better, and some
situations where it makes no difference at all and is entirely down to what
you / the organisation prefer.
Cheerio
Ian
"Nick Hounsome" wrote:
"Frans Bouma [C# MVP]" <pe******************@xs4all.nl> wrote in message
news:xn***************@news.microsoft.com... Nick Hounsome wrote:
"Jason Huang" <Ja************@hotmail.com> wrote in message
news:eC*************@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> In my C# Windows Form application project, I have done all queries
> on my codes.
> Now I wanna try using the Stored Procedure.
> But I am not clear about why using the stored procedure.
> Would some one give me some advice?
> Thanks for help.
1. To isolate the app from the database schema.
since when do you need a proc for that?
How else?
2. To allow simulation of inheritance and other OO features in the
database (i.e. a derived class will typically map to two tables and
SPs can hide this)
I can do that perfectly fine without a proc. If you truly want it
inside the db, you can also do it with a view. A view is even more
practical as well, as you don't have to hardcode filter statements in
the proc.
Additions and updates can be a problem through a view.
3. To allow the app to have access restricted to only those
operations and data that it needs.
you can also do that with different methods. (role based security).
Much harder to set up, document and maintain.
