Can you tell me what is the need for a stored procedure when there is UDF?
views:
83answers:
5
+1
A:
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.
Quassnoi
2010-02-02 14:42:23
Not to mention DDL as well.
Aaronaught
2010-02-02 14:44:25
`DDL` is a bad idea even in a procedure.
Quassnoi
2010-02-02 14:45:21
I didn't say it was a good idea, I just said you can do it. ;)
Aaronaught
2010-02-02 14:48:26
+2
A:
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
Randy Minder
2010-02-02 14:42:24
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 :)
Quassnoi
2010-02-02 14:46:22
What he meant was UDFs cannot use non-deterministic functions like GetDate() etc.
Ender
2010-02-02 14:54:53
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.
Daniel Pratt
2010-02-02 15:12:10
@Daniel: a non-deterministic function can be used in a computed column. Such a column, however, cannot be indexed.
Quassnoi
2010-02-02 15:25:55
A:
Sprocs are used to return output to an application. A UDF returns a table variable.
JonH
2010-02-02 14:43:32
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.
Aaronaught
2010-02-02 14:45:38
+1
A:
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.
Daniel Pratt
2010-02-02 14:54:14
+1
A:
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.
Matt Whitfield
2010-02-02 15:53:06