DML statements in SQL server functions

We can not write DML statements in SQL functions.

Functions have Only READ-ONLY database access. If DML operations would be allowed in functions, then functions would be pretty similar to stored procedures. The way it is, a stored procedure can use a function, but not vice versa. Therefore, functions increase the “power” of stored procedures, queries and T-SQL scripts.  UDF-s are also pitched as super-views.

User-defined functions are similar to stored procedures and can be executed as such, or they can be executed in the same fashion as a system function. Like stored procedures, they accept zero or more input parameters, but can return a scalar data type such as int, decimal, varchar, or sql_variant in addition to the new table data type.       The defining structure of a user-defined function is similar to that of a stored procedure. The main differences are the requirement to specify the RETURNS value and to terminate the function with the RETURN statement.       A user-defined function that returns a table data type can declare an internal table variable and return that variable as its return value. These are known as rowset functions, and can be used where table or view expressions are allowed in Transact-SQL queries. This means a table returned by a user-defined function can be referenced in the from clause of a Transact-SQL statement, while stored procedures that return resultsets cannot.       User-defined functions that return a table can be a powerful alternative to views. While views are limited to a single select statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views.”