Back to the list of all SQL Server 2000 / 2005 Interview Questions
|
|
What is the difference between a User Defined Function and a Stored Procedure in SQL Server?
|
|
Or
|
|
What is the difference between a UDF and a StoredProcedure in SQL Server?
|
|
The following are the differences between a User Defined Function and a Stored Procedure in SQL Server
|
- Stored Procedure support deffered name resolution where as functions do not support deffered name resolution.
- User Defined Function can be used in a select statement where as you cannot use a stored procedure in a select statement.
- UDF's cannot return Image, Text where as a StoredProcedure can return any datatype.
- In general User Defined Functions are used for computations where as Stored Procedures are used for performing business logic.
- UDF should return a value where as Stored Procedure need not.
- User Defined Functions accept lesser number of input parameters than Stored Procedures. UDF can have upto 1023 input parameters where as a Stored Procedure can have upto 21000 input parameters.
- You cannot use non-deterministic built-in functions in UDF's. For example functions like GETDATE() etc can not be used in UDFs, but can be used in Stored Procedures.
- Temporary Tables can not be used in a UDF where as a StoredProcedure can use Temporary Tables.
- UDF can not Execute Dynamic SQL where as a Stored Procedure can execute Dynamic SQL.
- User Defined Function does not support error handling where as Stored Procedure support error handling. RAISEERROR or @@ERROR are not allowed in UDFs.
|
Back to the list of all SQL Server 2000 / 2005 Interview Questions
|