Re: SQL Statement in C#

This is mainly MS-SQL thinking, with other databases(Oracle, db2,etc)
they do not think this way dynamic SQL is the prime method. The
people who have a problem with stored procedures have it with CRUD
statements, if you are doing something that multiple database pulls,
process the data, asking for more database info and then puts out a
simple string or number use a stored procedure the performance is
better then anything dynamic SQL on the client will give.

Now for the prime reason they give for using stored procedures for
CRUD.

Speed. Back in the old days of MS-SQL 6.5 stored procedures would be
compiled and kept around while dynamic would not so you gained a
decent speed increase. Since MS-SQL 7 this has not been the case
parameterized dynamic SQL and stored procedures are treated the same.
Where stored procedures can give a speed boost is that there is a
better chance that the same plan will be use. However stored
procedures use alot of coalease and isnull statements and those are
very CPU intensive.

Security. Compared to other database the security in MS-SQL is
garbage. However with stored procedures you do start to add security
because you only have to give users access to the stored procedures
and not the table themselves. the stored procedures have the
permissions to the tables and that is given internally. For client/
server applications this can be useful since you don't have to any
chance of the user of digging up the password, however with web and
other 3-physical tier designs this is not the case since the user
would not have the direct connections to the database. Also the
previously mentioned SQL injection attacks

That is the basis of it check of the following links for more detailed
talks

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
http://www.codinghorror.com/blog/archives/000117.html




On Feb 16, 5:25 pm, Dylan Parry <use...@xxxxxxxxxxxxxx> wrote:
Peter Bradley wrote:
Don't do it. Use astoredprocedure.

I've heard people saying this many times in the past, and while I don't
want to get into the politics of it, I've never used them simply because
I don't know how to! Any pointers for a self-confessed "n00b"?

--
Dylan Parryhttp://electricfreedom.org|http://webpageworkshop.co.uk

Programming, n: A pastime similar to banging one's head
against a wall, but with fewer opportunities for reward.


.

Relevant Pages

  • Re: Inline SQL or stored procs for my C# windows app?
    ... how hard is it to move a stored procedure from one database to ... If you have dynamic sql, then you are going to run into the same ... the sql) to make sure you are not using any database-specific features. ... (you won't have to worry about changing how you call stored procedures from ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Newbie: Stored Procedures and Or Datasets ?
    ... > be a concensous that Stored Procedures are the most effective means to ... > mena less database access, can be optimised by the DBMS or Database ... you can use Stored Procedures just as easily as Dynamic Sql - in it's ... statement is virtually identical to calling a stored proc. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Views, Table Permissions and Stored Procedures
    ... The Curse and Blessings of Dynamic SQL ... one database handles one or more clients. ... >client and use views to limit the viewable data based on the user id in the ... All works fine until we run stored procedures that have dynamic sql ...
    (microsoft.public.sqlserver.security)
  • Re: Views, Table Permissions and Stored Procedures
    ... > dynamic SQL as there can be ways around using it. ... >>We have an application where clients have their database at a central ... >>back data for client A. There is no way for Client A to view Client B's ... All works fine until we run stored procedures that have dynamic sql ...
    (microsoft.public.sqlserver.security)
  • Re: Database Model - Class, objects and interaction
    ... I was just proving stored procedures can call views. ... stuff like security and logging. ... So given 100,000 users, you would create database accounts for each? ... Part of our system's integrity was its reliability, and reliability is often assisted by simplicity. ...
    (comp.object)