Introduction
Stored Procedure is nothing but a precompiled SQL statement. This means that once we compile a query, it stores the result and the next time we don't need to compile it again and again. It means it is prepared SQL code that you can save and reuse later multiple times. It is used to insert data, modify data, and delete data in a database table. There is no need to write SQL commands over and over again. You just need to execute a stored procedure instead.
Stored Procedure Syntax
- Create Procedure Procedure_Name
- as
- sql_statement
- Go
For the Execution of a Stored Procedure Syntax
Exec Procedure_Name;
Example
Like in the above example, we created a stored procedure with the name of SelectAllEmployees and executed it using the Exec keyword. Like Select, we can write insert, update, and delete the stored procedure.
Let's try to understand by passing a parameter in the Stored Procedure
Above, we pass EmpCode as a parameter in Stored Procedure and at the time of executing, we pass the value of EmpCode so we can fetch the record f that EmpCode. We can pass multiple parameters as well in the stored procedure. Just make sure you pass those parameter values at the time of executing the stored procedure. It's good practice to avoid a stored procedure name starting with 'Sp'.If you start a stored procedure name with 'Sp', then it will impact your query performance. So it's better to avoid this and give a meaningful name.
Difference between a Stored Procedure and a Function
- Stored Procedure is a precompiled SQL statement.
- A function is a user-defined function that can perform an action and return a result.
- Stored procedures can have an input as well as an output parameter.
- With a function, we only have an input parameter.
- A Stored Procedure can return zero or multiple values.
- A function must return a single value.
- We can use a try-catch block in a stored procedure.
- In a function, we can't use a try-catch block.
- We can call a function from a stored procedure.
- We can't call a Stored Procedure from a function.
- We can use Transactions in a stored procedure, but in a function, we cannot use it.
I hope you have a better understanding now on Stored Procedures and Functions, at least basic as well as advanced. Hopefully, after reading this article you will get a better idea of how to use Stored Procedures and Functions.