When Should I Use Stored Procedures

When should I use stored procedures?

Wow... I'm going to swim directly against the current here and say, "almost always". There are a laundry list of reasons - some/many of which I'm sure others would argue. But I've developed apps both with and without the use of stored procs as a data access layer, and it has been my experience that well written stored procedures make it so much easier to write your application. Then there's the well-documented performance and security benefits.

Is the usage of stored procedures a bad practice?

That's not a SP problem, that's an issue of your development process. If you have no information you need - just get it.

You can make a simple visual map that shows your table schema and dependant SPs. If your DB is too large for visual mapping, add common text file that consists of your SPs and names of tables it depends upon.

Anyway, the bigger your DB is, the worse will be your job when inlining details of your schema into your application code. When you use SP you guarantee that this functionality won't be doubled and that most changes will occur on DB side without application recompilation and redistribution.

Upd

I forgot to mention one other thing. Good DB tools provide easy way to find dependant tables for each SP. There's 'View Dependencies' item in SP context menu in Microsoft SQL Management Studio, for example.

When should I use stored procedure or function?

In 2014, for CRUD functions (INSERT - SELECT - UPDATE - DELETE) you should use an ORM to create a code representation of the data model.

If you need to perform a lot of complex SQL statements from a small set of input or if you you need to push thousands of records to SQL in a single statement, I would recommend you to use Stored Procedure.

Some people will probably talk about speed of stored proc. A decade ago, It was true about the speed but now, in most of time, there's no performance gain by using a SP.

Take a look to that article: http://kevinlawry.wordpress.com/2012/08/07/why-i-avoid-stored-procedures-and-you-should-too/

MySQL stored procedures use them or not to use them

Unlike actual programming language code, they:

not portable (every db has its own version of PL/SQL. Sometimes different versions of the same database are incompatible - I've seen it!)
not easily testable - you need a real (dev) database instance to test them and thus unit testing their code as part of a build is virtually impossible
not easily updatable/releasable - you must drop/create them, ie modify the production db to release them
do not have library support (why write code when someone else has)
are not easily integratable with other technologies (try calling a web service from them)
they use a language about as primitive as Fortran and thus are inelegant and laborious to get useful coding done, so it is difficult to express business logic, even though typically that is what their primary purpose is
do not offer debugging/tracing/message-logging etc (some dbs may support this - I haven't seen it though)
lack a decent IDE to help with syntax and linking to other existing procedures (eg like Eclipse does for java)
people skilled in coding them are rarer and more expensive than app coders
their "high performance" is a myth, because they execute on the database server they usually increase the db server load, so using them will usually reduce your maximum transaction throughput
inability to efficiently share constants (normally solved by creating a table and questing it from within your procedure - very inefficient)
etc.

If you have a very database-specific action (eg an in-transaction action to maintain db integrity), or keep your procedures very atomic and simple, perhaps you might consider them.

Caution is advised when specifying "high performance" up front. It often leads to poor choices at the expense of good design and it will bite you much sooner than you think.

Use stored procedures at your own peril (from someone who's been there and never wants to go back). My recommendation is to avoid them like the plague.

When should I use stored procedures (in mysql)?

There are a couple of times when procedures are interesting to me:

When I want to entirely encapsulate access to the database by forcing apps to use the stored procedures. This can be good for an organization with a strong/large database group and a small/weak programming team. It's also helpful when you have multiple code bases accessing the database, because they all get one interface, rather than each writing their own queries, etc.
When you're repetitively doing something that should be done in the database. You'll know it when you see it.

So, unless you're a serious control freak (or are building some huge system like a bank), 90% of the time you won't need them.

What is the difference between a stored procedure and a view?

A view represents a virtual table. You can join multiple tables in a view and use the view to present the data as if the data were coming from a single table.

A stored procedure uses parameters to do a function... whether it is updating and inserting data, or returning single values or data sets.

Creating Views and Stored Procedures - has some information from Microsoft as to when and why to use each.

Say I have two tables:

tbl_user, with columns: user_id, user_name, user_pw
tbl_profile, with columns: profile_id, user_id, profile_description

So, if I find myself querying from those tables A LOT... instead of doing the join in EVERY piece of SQL, I would define a view like:

CREATE VIEW vw_user_profile
AS
  SELECT A.user_id, B.profile_description
  FROM tbl_user A LEFT JOIN tbl_profile B ON A.user_id = b.user_id
GO

Thus, if I want to query profile_description by user_id in the future, all I have to do is:

SELECT profile_description FROM vw_user_profile WHERE user_id = @ID

That code could be used in a stored procedure like:

CREATE PROCEDURE dbo.getDesc
    @ID int
AS
BEGIN
    SELECT profile_description FROM vw_user_profile WHERE user_id = @ID
END
GO

So, later on, I can call:

dbo.getDesc 25

and I will get the description for user_id 25, where the 25 is your parameter.

There is obviously a lot more detail, this is just the basic idea.

What's the point of a stored procedure?

Stored procedures are code that runs on the database server.

They have a number of uses. Think: If I could run code directly on the database server, what could I use that for?

Among their many uses, stored procedures can be used to shift some of the processing load to the database server, to reduce network traffic, and to improve security.

http://en.wikipedia.org/wiki/Stored_procedure