SQL Server – User Defined Functions vs. Stored Procedures

SQL Server user-defined functions and stored procedures have almost similar functionality. Both allow you to create a batch of SQL statements to execute on SQL server.

Benefits:

• We can reuse the code from one program to another, this way we can increase the productivity.
• Centralize control, to make business logic changes in a single place that automatically affect all dependent applications.

Difference between User Defined Functions and Stored Procedures

• Stored procedures are called independently, using the EXEC command.
  Example: EXEC dbo.procedure_name ‘Parameter1’Functions are called within another Standard SQL statement.
  Example:  SELECT dbo.function_name(‘Parameter1’)

• Stored procedures allow us to enhance application security by granting users and applications permission to use stored procedures instead of given the permission to access the tables. It provides the ability to restrict user actions.
• Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.

If you have any other idea, please share as comment.