difference between function and stored procedures

Dipa Mehta

6y1.2kLearn iOS Programming

What is the difference between function and stored procedure in .Net technologies? kindly explain.

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sanwar Ranwa6y ago
Accepted answer
Hi

Following are main difference between sp and funcation
1. Store Procedure can return zero or n values whereas function can return one value .
2. Store Procedures can have input, output parameters but functions can have only input parameters.
3. Store Procedure allows select as well as DML(INSERT/UPDATE/DELETE) statements but function allows only select statement
4. Functions can be called from procedure but procedures cannot be called from function.
5. We can call a procedure using Execute or Exec command whereas the function is called using the Select command.
+5
Sam Hobbs9mo ago
I will assume you are asking about SQL Server since .Net does not have Stored Procedures. In .Net there are no functions, everything that might be called a function or procedure is called a method.
I asked Perplexity AI about one of the responses here and it found a StackOverflow answer that has the response posted here. See:
https://www.perplexity.ai/search/is-any-of-the-following-false-QWUS4nE2TW69IPfa7Ncupg#0
The following is a summary:
- In computer science, functions can change other things (they are called side effects)
- A function does not have to have parameters
- In SQL Server:
  a user-defined function must return a value
  A SQL function can have zero parameters
  SQL functions ... can read data and, in some systems, have observable behaviors like raising errors
  The “cannot alter the data they receive as parameters” is misleading
At the end Perplexity AI says that the following is a technically defensible one-liner for SQL Server.
In SQL Server, a user-defined function must return a value (scalar or table) and is restricted from performing data modifications, while a stored procedure may have zero or more parameters, can modify database state, and may or may not return values (status codes, output parameters, or result sets).
I also asked Perplexity AI about the currently accepted response. It says that Several of those lines are misleading or incomplete for SQL Server; see:
https://www.perplexity.ai/search/is-any-of-the-following-inaccu-cZz8JFmpSKWfvo3uAMWRaQ#0
0
Ajay Gohil6y ago
Here I have tried to summarize the differences between functions and procedures:
A FUNCTION always returns a value using the return statement. PROCEDURE may return one or more values through parameters or may not return any at all.

Functions are normally used for computations where as procedures are normally used for executing business logic.

A Function returns 1 value only. Procedure can return multiple values (max 1024).

Stored procedure always returns an integer value of zero by default. Whereas function return types could be scalar or table or table values.

Stored procedures have a precompiled execution plan, where as functions are not.

A function can be called directly by SQL statement like SELECT func_name FROM DUAL while procedures cannot.

Stored procedure has the security and reduces the network traffic and also we can call stored procedure in any number of applications at a time.

A Function can be used in the SQL queries while a procedure cannot be used in SQL queries. That causes a major difference between function and procedures.
0
Mageshwaran R6y ago
Functions follow the computer-sciency definition in that they MUST return a value and cannot alter the data they receive as parameters (the arguments). Functions are not allowed to change anything, must have at least one parameter, and they must return a value. Stored procs do not have to have a parameter, can change database objects, and do not have to return a value.
0
Puneet Kankar6y ago
Hello,

Below are a basic difference in function and stored produces in SQL .

1. The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values.

2. Functions can have only input parameters for it whereas Procedures can have input or output parameters.

3. Functions can be called from Procedure whereas Procedures cannot be called from a Function.

4. The procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

5. Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

6. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

7. Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.

8. Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

9. An exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a Function.

10. We can use Transactions in Procedure whereas we can't use Transactions in Function.
+1
Jaimin Shethiya6y ago
Hello,

Following are the difference between SP and UDF.
You can use Transactions in Procedure. But, you can’t Transactions used in Function.
By using a try-catch block, an exception can be handled in a Procedure. Whereas, you can’t use try-catch block in a Function to handle the exception.
Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
Thanks
+2
Arun Kumar Tiwari6y ago
These are the main differences between sp and fun.

1. Store Procedure can return zero or n values whereas function can return one value.
2. Store Procedures can have input, output parameters but functions can have only input parameters.
3. Store Procedure allows select as well as DML(INSERT/UPDATE/DELETE) statements but function allows only select statement
+1