Difference b/w SPs & Functions

1. cannot hv tran in functions
2. cannot include SPs in select queries or in from clause whereas functions
can be included. (exception using OPENQUERY for including SP in from clause)
What other differences?Hi Rakesh
Difference between Stored Procedure and UDF:
Returning Parameters:
UDF Should return a parameter
Stored Procedure need not return any value
UDF Returns only one parameter
Stored Procedures can return more than one parameters
UDF can return a table
Stored Procedure returns a resultset
Usage
UDFs can be used a inline functions and can also be executed explicitly
eg: inline function: SELECT getdate()
Stored Procedures cannot be used as inline functions
Hope this helps you.
Please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Rakesh" wrote:

> 1. cannot hv tran in functions
> 2. cannot include SPs in select queries or in from clause whereas function
s
> can be included. (exception using OPENQUERY for including SP in from claus
e)
>
> What other differences?|||You cannot use INSERT/UPDATE/DELETE on base tables.
You cannot Use NonDeterministic functions inside UDF
A scalar valued user defined function can be used Anywhere a value is
expected, as in WHERE clause, check contraint etc.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:8DDCFBA3-C85D-47A4-A2CE-CDC22EEA364F@.microsoft.com...
> 1. cannot hv tran in functions
> 2. cannot include SPs in select queries or in from clause whereas
> functions
> can be included. (exception using OPENQUERY for including SP in from
> clause)
>
> What other differences?|||Rakesh
Visit at http://www.sql-server-performance.com/
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:8DDCFBA3-C85D-47A4-A2CE-CDC22EEA364F@.microsoft.com...
> 1. cannot hv tran in functions
> 2. cannot include SPs in select queries or in from clause whereas
> functions
> can be included. (exception using OPENQUERY for including SP in from
> clause)
>
> What other differences?|||"Chandra" wrote in message

> Difference between Stored Procedure and UDF:
> Returning Parameters:
> UDF Should return a parameter
> Stored Procedure need not return any value
Stored Procedures RETURN a value, even if you dont explicitly specify (0 by
default)

> UDF Returns only one parameter
> Stored Procedures can return more than one parameters
Stored Procedure can RETURN one one value and that must be integer.
You can use Output Parameters to get other datatypes back from the SP

> UDF can return a table
> Stored Procedure returns a resultset
Stored Procs can return multiple recordsets, UDF can return only one (table
valued udfs)

> UDFs can be used a inline functions and can also be executed explicitly
> eg: inline function: SELECT getdate()
What you mean by inline?

> Stored Procedures cannot be used as inline functions
EXEC yourvar= ProcName <ParamList>
Just plain Nitpicking :)
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com