why/when Do I need stored Procedures?

Hi!

I'm building a website with a database .
The users would be able to insert data through user interface. This data would be inserted in tables in the db. The same data could be rertived according to the search options. It supposed to be used like the add classfied
website.

Everyone tell me that i have to use stored procedures in my db. why do i need it? why can't i use a simple "insert" query in order to take the data from the user


Plz give ne a good explanation[/COLOR]

Progfrog

 

You're developing an ASP.NET site rite? Well then there's nothing wrong, you'll definitely need to create stored procedures. Basically stored procedures are like "methods" for SQL statements to interact with your database, so let's say when you need your page to be able to create a new user for example, you'll call a method from your c# codes which will then execute the corresponding stored procedure with the inputs as the variables.

 

You don't need to use SP's. They can make development a lot easier and queries a lot quicker because you are eliminating a lot of the site -> database transfer, but you don't have to have them.

They're most useful when the same query will have to be run many different times. They are also convenient on highly complex queries that junk up your application, and may later change. Depending on how you use them they are also great for creating a more stable application environment, because you can change the query externally as long as the interface remains the same.

 

Here are some of the reasons why stored procedures are recommended:
1. SQL Server stores your execution plan... This is what makes the query to run faster in subsequent execution
2. It's more secured to use Stored Proc. The security issue comes up when you are using dynamic sql. The end user could manipulate your query from the webapp. Using storedproc programmatically forces the programmer to use parameter. This in turn can avoid the malicious input.
3. You're acting in disciplined manner and conforming to best practice known to the SQL Server community.

 

However, if you want to code a simple, small site, you don't need to use stored procedures. It's one less place to debug when testing your app.