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

Question

Can you tell me what is the need for a stored procedure when there is UDF?

Randy Minder · Accepted Answer · 2010-02-02 14:42:24Z

2

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

answered Feb 2, 2010 at 14:42

Randy Minder

48.8k55 gold badges221 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Quassnoi Over a year ago

A UDF can return a result set and perform non-deterministic activity too. It would be a table-valued function and a non-deterministic function, respectively :)

Ender Over a year ago

What he meant was UDFs cannot use non-deterministic functions like GetDate() etc.

Daniel Pratt Over a year ago

If that is, in fact, what he meant, then he's still wrong. The only restriction that I am aware of regarding non-deterministic functions is that a function that uses them cannot be used in the context of a calculated column.

Quassnoi Over a year ago

@Daniel: a non-deterministic function can be used in a computed column. Such a column, however, cannot be indexed.

Quassnoi · Accepted Answer · 2010-02-02 14:48:19Z

1

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.

edited Feb 2, 2010 at 14:48

answered Feb 2, 2010 at 14:42

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

2 Comments

Quassnoi Over a year ago

DDL is a bad idea even in a procedure.

Aaronaught Over a year ago

I didn't say it was a good idea, I just said you can do it. ;)

Daniel Pratt · Accepted Answer · 2010-02-02 14:54:14Z

1

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.

answered Feb 2, 2010 at 14:54

Daniel Pratt

12.1k2 gold badges47 silver badges62 bronze badges

Comments

Matt Whitfield · Accepted Answer · 2010-02-02 15:53:06Z

1

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.

answered Feb 2, 2010 at 15:53

Matt Whitfield

6,6343 gold badges31 silver badges47 bronze badges

Comments

JonH · Accepted Answer · 2010-02-02 14:43:32Z

0

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

answered Feb 2, 2010 at 14:43

JonH

33.2k13 gold badges93 silver badges149 bronze badges

1 Comment

Aaronaught Over a year ago

No. A UDF can return a scalar result, or an inline query. A UDF does not have to return a table variable, and a Stored Procedure may return no output at all.

Collectives™ on Stack Overflow

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

5 Answers 5

4 Comments

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related