Stored Procedures vs. User Defined Functions in SQL Server
1) SQL Server user-defined functions and stored procedures offer similar functionality. 2) Both allow you to create bundles of SQL statements that are stored on the server for future use.
Benefit
1) you can save programming time by Reusing code from one program to another,
2) cutting down program development time.
3) Hiding the SQL details,
4) Allowing database developers to work with SQL and application developers to deal only in application programming.
5) Centralize maintenance, allowing you to make business logic changes in a single place that automatically affect all dependent applications.
Difference
1) Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.
2) Stored procedure allow you to enhance application security by granting users and applications permission to use stored procedures, rather than permission to access the underlying tables.
3) 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.
4) You can not write exec('Insert Into ...') in UDF.
5) you couldn't do updates in a user defined function.
