What is the difference between Stored Procedure and Function?

 Posted by Tripati_tutu on 3/11/2011 | Category: Sql Server Interview questions | Views: 15158 | Points: 40

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 | Alert Moderator 

Comments or Responses

Posted by: PandianS on: 3/13/2011 | Points: 10
Could you please change some of the points...

- The stored procedures are compiled at very first time or it compiles at every time whenever the schema, some SET functions, Recompile hint used.

- Execute or Exce is not mandatory to execute the stored procedure. But, It's recommended thing. But when using the function SELECT or SET or PRINT is required.

- Function also can return single, multiple value as like as stored procedure does. (The function can be a scaler or table valued)

Posted by: Jatinnahar15 on: 4/21/2011 | Points: 10
for better understanding of diff between stored procedure and UDF please vsist this link aslo


Login to post response