There are few differences between Stored Procedure and Function that are
• Stored Procedures are stored in a compiled format into the database where as Functions are compiled at run time.
• The Stored Procedures can perform certain tasks in the database by using insert, delete, update and create commands but in function you can't use these commands.
• Normally the Stored procedures are used to process certain task but the Functions are used to compute the values that is we can pass some value as input and then it perform some task on the value and return output.
• Stored procedures can change in the server directly but Functions cannot change in the server directly.
• To run a Stored Procedure we have to use the Execute or Exec command where as Functions can run as an executable file.
• Stored Procedure can return multiple values where as Functions can return only single value.
• The Stored Procedures can be used directly in the program by using its commandtype but Functions can be used by using SQL Query.
• The Stored Procedures are having both IN and OUT parameter where as Functions are always having IN parameter, no OUT parameter is possible.
• Stored Procedures cannot be used as an inline with a select statement while Functions can.
• The temparary variable is required to hold the return value of a Stored Procedure but in Functions, the temporary variable is optional.
Asked In: Many Interviews |