SQL Server: what can a stored procedure do that a user defined function cannot?

Can you tell me what is the need for a stored procedure when there 开发者_开发知识库is UDF?

Off the top of my head, a stored procedure can do the following that a UDF cannot do:

1) Modify Data

2) Return a result set to a client

3) Perform non-deterministic activity

A procedure can run DML, a function cannot.

In general, a function is designed to be used as a part of the query, while a stored procedure is a batch of SQL statements run together implementing some business logic, possibly with different credentials.

A function cannot directly alter or update the database in any way, either via DML (INSERT, UPDATE, DELETE, ect) statements or DDL (CREATE TABLE, etc) statements. It also cannot do anything that might indirectly result in database modifications, such as executing ad-hoc SQL statements (of any sort) or executing stored procedures.

One key difference is that UDFs always have an output of fixed schema, stored procedures can result an arbitrary number of result sets in an arbitrary format.

Sprocs are used to return output to an application. A UDF returns a table variable.