Differences between Stored procedures and User defined functions

1
Stored procedures may or may not return values
But function should return value

2
Stored procedure cannot be used in the select/where/having clause
But function can be called from select/where/having clause

3
Stored procedure can run independently. It can be executed using EXECUTE or EXEC command
But function cannot run independently

4
TRY CATCH statements can be used in the stored procedures.
But it cannot be used in the function. But we can use raise error function.

5
Stored procedure can call the user defined functions
But the function cannot call the stored procedures.

6
Stored procedures can have input and output parameters.
But the function can have only input parameters.

7
Stored procedures can have select and all DML operations.
But the function can do only select operation.

8
Stored procedures can use all the data types available in sql server.
But the function cannot use the ntext, image and timestamp data types as return type.

9
Stored procedure can have the dynamic sql statement and which can be executed using sp_executesql statement.
But the function cannot execute the sp_executesql statement.

10
Stored procedure allows getdate () or other non-deterministic functions can be allowed.
But the function won't allow the non-deterministic functions.

What is User-defined Functions? What are the types of User-defined Functions that can be created?

There are three types of User-Defined functions in SQL Server:

• Scalar Function
• Inline Function
• Multi-statement Table-valued Function

Scalar UDFs
Scalar UDFs return a single value. They are similar to built-in functions such as GETDATE(), or OBJECT_NAME(), which return a single string, date, or integer. The value returned by a scalar UDF can be based on the parameters passed.
Scalar UDFs can return any scalar system-supplied data type, except TIMESTAMP. You cannot return values with a user-defined data type from scalar UDFs. If you want to do so, you must specify the underlying system-supplied data type instead.

In-line UDF
In-line UDFs return a single row or multiple rows and can contain a single SELECT statement. Because in-line UDFs are limited to a single SELECT, they can't contain much logic. They can be effective, however, for lookups that return multiple values, such as the top five best-selling books with title, author, and publication date.

Multi-statement UDFs
The multi-statement UDFs can contain any number of statements that populate the table variable to be returned. Notice that although you can use INSERT, UPDATE, and DELETE statements against the table variable being returned, a function cannot modify data in permanent tables. Multi-statement UDFs come in handy when you need to return a set of rows, but you can't enclose the logic for getting this rowset in a single SELECT statement.