stored procedure
i can write sql queries on aspx page
or
i can write stored procedure which will be in the server
What is the plus point in using stored procedure and what are the draw backs
can we use only stored procedures in our project.
[261 byte] By [
syedwna] at [2007-11-11 10:17:44]
# 1 Re: stored procedure
The advantages of having stored procedures:
* Stored procedures execute faster since they are already compiled and closer to the database.
* You can always modify a stored procedure for change in requirements without impacting the code.
The disadvantage I can think of:
* You tie up the business logic to the database. This reduces the flexibility for application development.
# 2 Re: stored procedure
Modern versions of SQL Server (7.0 and above) cache execution plans for ad hoc queries as well as for stored procedures, so stored procs are not necessarily faster. They do, however, offer more granular security: rather than granting a user read/write access to entire tables, which you must do for ad hoc queries, you may grant them permission to specific stored procedures.
For more information, see http://www.google.com/search?q=stored+procedure+dynamic+sql+performance
# 3 Re: stored procedure
ad hoc queries means what.
# 4 Re: stored procedure
An "ad hoc query" is SQL code in your application, as opposed to a stored procedure in the database.
# 5 Re: stored procedure
My two cents:
Also, ad hoc queries can leave you vulnerable to SQL Injection, which can potentially leave your database wide open to attackers, unless you write quite a lot of validation code, and even then you'll never be certain...
Stored Procedures are much much safer.
-Andrew
# 6 Re: stored procedure
Well, not necessarily. ;-) Dynamic SQL (a.k.a. string concatenation) leaves you vulnerable to SQL injection. All dynamic SQL statements are ad hoc queries, but not all ad hoc queries use dynamic SQL. For example, this query:
SELECT col1, col2,
FROM tablename
WHERE col1 = @param
could appear in your code. In that case, it would be an ad hoc query, but because it uses a parameter rather than string concatenation, it is not vulnerable to SQL injection.
# 7 Re: stored procedure
OK, Phil. Picky, Picky. ;-)
I did say "can leave you vulnerable...", not "will definitely leave you vulnerable..."
Perhaps I did simplify it a bit. In my experience, the usual reason for using ad hoc queries is because the person using them doesn't know how to use a parameterized SPROC to replace an ad hoc query using string concatenation, or thinks it's too much work, and so they use an ad hoc query with string concatenation.
But I didn't want to get into all that...SQL Injection is such a huge potential problem that I'd rather just say "avoid ad hoc queries" and leave it at that. Also, my understanding is that SQL Server has a better chance of pre-compiling a stored Proc than an ad hoc query, though I could be wrong...My rule of thumb has always been "use SPROCS wherever possible with SQL Server".
-Andrew
# 8 Re: stored procedure
There's a huge ongoing debate in the .NET community re: stored procedures vs. ad hoc SQL. Rob Howard started it with this blog post (http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx). Several people (primarily vendors of ORM tools which generate ad hoc SQL) jumped all over the post (http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx) as being inaccurate and too general.
My rule of thumb is "avoid string concatenation in SQL queries wherever possible." (That includes string concatenation within stored procs, which people rarely mention.) The only real advantage of stored procs over parameterized ad hoc SQL in an encapsulated data access layer is granular security.
# 9 Re: stored procedure
what is this SPROCS
# 10 Re: stored procedure
It's an abbreviation for "Stored PROCedures".
