Difference Between Stored Procedures and User Defined Functions

Function vs. Stored Procedure in SQL Server

Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed).

A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set.

A point worth noting from comments, which summarize the answer. Thanks to @Sean K Anderson:

Functions follow the computer-science definition in that they MUST return a value and cannot alter the data they receive as parameters
(the arguments). Functions are not allowed to change anything, must
have at least one parameter, and they must return a value. Stored
procs do not have to have a parameter, can change database objects,
and do not have to return a value.

performance difference between User Defined Function and Stored Procedures

There is no difference in speed between a query run inside a function and one run inside a procedure.

Stored procedures have problems aggregating results, they cannot be composed with other stored procedures. The onyl solution is really cumbersome as it involves catching the procedure output into a table with INSERT ... EXEC ... and then using the resulted table.

Functions have the advantage of being highly composable as a table value function can be placed anywhere a table expression is expected (FROM, JOIN, APPLY, IN etc). But functions have some very severe limitations in terms of what is permitted in a function and what is not, exactly because they can appear anywhere in a query.

So is really apple to oranges. The decision is not driven be performance, but by requirements. As a general rule anything that returns a dataset should be a view or a table valued function. Anything that manipulates data must be a procedure.

Functions vs Stored Procedures

If you're likely to want to combine the result of this piece of code with other tables, then obviously a table-valued function will allow you to compose the results in a single SELECT statement.

Generally, there's a hierarchy (View < TV Function < Stored Proc). You can do more in each one, but the ability to compose the outputs, and for the optimizer to get really involved decreases as the functionality increases.

So use whichever one minimally allows you to express your desired result.

What's the differences between stored procedures, functions and routines?

Google is your friend. The first match for "mysql routine function procedure" is this: http://dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html

A quick summary:

A stored routine is either a procedure or a function.
A procedure is invoked using a CALL statement and can only pass back values using output variables.
A function can be called from inside a statement just like any other function and can return a scalar value.

MySQL stored procedure vs function, which would I use when?

You can't mix in stored procedures with ordinary SQL, whilst with stored function you can.

e.g. SELECT get_foo(myColumn) FROM mytable is not valid if get_foo() is a procedure, but you can do that if get_foo() is a function. The price is that functions have more limitations than a procedure.

What is the need for user-defined functions in SQL Server?

Everything we can accomplish using functions can be done by stored procedures itself,

This is false.

Stored procedures cannot be recomposed or inlined (like how a VIEW or Table-Valued Function can - and SQL Server 2017 can now inline scalar functions too).
Stored procedures cannot be proven "safe" by the database engine (as stored procedures can perform DML/DDL operations, whereas UDFs can only perform read-only operations).
Stored procedures cannot be used to perform operations on scalar values.

You can make the argument that everything you can do inside a query that would use a UDF could be done without that UDF - and this is technically true (because stored procedures are Turing Complete), but it's like saying we don't need for, while, try/catch, using, or async in C# because surely if and goto is good enough for everyone!

Code-quality, maintainability, and runtime performance matters - appropriate use of UDFs results in not only aesthetically beautiful queries, but also better performance thanks to inlining, recomposition, expression tree pruning, and so on.

Besides, who is using stored procedures today when all the cool kids are using Entity Framework? :D

Footnote

I want to elaborate on my remark:

Stored procedures cannot be used to perform operations on scalar values.

While it's true that stored procedures have return values and output parameters - they still aren't classed the same as true UDFs because a stored procedure return value can only be an int value (they're closer to program exit codes in POSIX/Win32 than a real return value) and stored procedure output parameters cannot be used inside another query unless you're doing ugly hacks with CURSOR or Dynamic SQL (Injection).