Difference between stored procedures and user defined functions
SqlDatabaseStored ProceduresSql Problem Overview
Can anyone explain what is the exact difference between stored procedures and user defined functions, and in which context each is useful?
Sql Solutions
Solution 1 - Sql
This is what i always keep in mind :)
- Procedure can return zero or n values whereas function can return one value which is mandatory.
- Procedures can have input/output parameters for it whereas functions can have only input parameters.
- Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
- Functions can be called from procedure whereas procedures cannot be called from function.
- Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
- We can go for transaction management in procedure whereas we can't go in function.
- Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
- UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
- UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
- Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
Source http://www.codeproject.com/Tips/286539/Difference-between-stored-procedure-and-function
Solution 2 - Sql
A function always returns a value, and can not perform DML statements (INSERT/UPDATE/DELETE).
A stored procedure can not return a value - you need to use an OUT parameter - and can run DML statements.
Advantage of Using a Function vs a Stored Procedure?
Aside from the comparison above, they are equal. But given the comparison, depending on what you need to do it's likely you will use a stored procedure more often than you will a function.
Solution 3 - Sql
User defined function has few limitiations like DML statments canbe used etc pls check
Solution 4 - Sql
Differences:
-
Procedures can accept input(default), output and inout type parameters for it. Functions can accept only input type parameters.
-
Procedures may or may not return a value or may return more than one value using the OUTPUT and/or INOUT parameters. A procedure may return upto 1024 values through OUTPUT and/or INOUT parameters. Function always returns only one value.
-
Stored procedure returns always integer value by default zero. Function return type could be scalar or table or table values.
-
Stored procs can create a table but can’t return table. Functions can create, update and delete the table variable. It can return a table
-
Stored Procedures can affect the state of the database by using insert, delete, update and create operations. Functions cannot affect the state of the database which means we cannot perform insert, delete, update and create operations operations on the database.
-
Stored procedures are stored in database in the compiled form. Function are parsed and conpiled at runtime only.
-
Stored procs can be called independently using exec keyword. Stored procedure cannot be used in the select/where/having clause. Function are called from select/where/having clause. Even we can join two functions.
-
Normally stored procedure will be used for perform specific tasks. Functions will be used for computing value. Stored procedure allows getdate () or other non-deterministic functions can be allowed. Function won’t allow the non-deterministic functions like getdate().
-
In Stored procedures we can use transaction statements. We can’t use in functions.
-
The stored procedures can do all the DML operations like insert the new record, update the records and delete the existing records. The function won’t allow us to do the DML operations in the database tables like in the stored procedure. It allows us to do only the select operation. It will not allow to do the DML on existing tables. But still we can do the DML operation only on the table variable inside the user defined functions.
-
Temporary tables (derived) can be created in stored procedures. It is not possible in case of functions.
-
When sql statements encounters an error, T-SQL will ignore the error in a SPROC and proceed to the next statement in the remaining code. In case of functions, T-SQL will stop execution of next statements.
Refer this link :