What is Stored Procedure in SQL Server?
Stored Procedure in SQL Server means it has pre-compiled SQL statements the users use frequently.
Generally, users can create stored procedures and the SQL server will have an execute plan or format saved for it.
Once it is called, the SQL Server will run the procedure based on the execution format or the compiled code.
Certainly, a stored procedure in SQL Server is a database object. Its inputs are given through input parameters and output is displayed through output parameters as per pre-compiled.
Also, they are handy as they are pre-compiled objects and saved until it is altered further. A set of tasks can be given for the stored procedure which is performed beforehand returning the output.
Create procedure syntax
CREATE PROCEDURE procedure_name
What is Function in SQL Server?
Function in SQL Server is a set of SQL statements.
Certainly, they are also pre-compiled and called or executed by the function name as per the requirement.
Function in SQL Server is a database object. Users can send inputs through the input parameters but it can output one value as well as a table.
Create function syntax
CREATE FUNCTION function_name()
What is the difference between a Stored procedure and a function?
There are many differences between the Stored procedure and function.
Following are the key differences between a Stored procedure and function in SQL Server:
1. Stored procedure can return numerous values, but a function can return a single value.
2. Stored procedure's return value is optional, but a function must return a value.
3. Stored procedure can have input and output parameters, but a function can have only input parameters.
4. Stored procedures can handle errors through the
TRY-CATCH block but a function cannot handle errors via the
5. Stored procedure supports
DELETE statements but a function supports only SELECT as it doesn't support
DML statements namely INSERT, UPDATE, and DELETE.
6. Stored procedure assists to execute
transactions but a function doesn't assist to execute transactions.
7. A function can be executed within a stored procedure, but a stored procedure doesn't work within a function.
8. SELECT statements cannot be used to call a Stored procedure but SELECT statements can be used to call a function.