A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, StoreProc, sp or SP) is actually stored in the database data dictionary.
In this article I will covered about sql stored procedures, creating a stored procedure and exec stored procedure with example.
What is SQL Stored Procedures?
A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure.
A stored procedure in SQL Server is similar to a procedure in other programming languages, Its a precompiled collection of Transact-SQL statements stored under a name and processed as a unit.
- It can accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
- It can contain programming statements that perform operations in the database, including calling other procedures.
- It can return a status value to a calling procedure or batch to indicate success or faliure (and the reason for failure)
Advantages of using stored procedures
- Modular programming
- Faster Execution
- Reduction in network traffic
- Efficient reuse of code and programming abstraction
- Can be used as a security mechanism (Grant users permission to execute a stored procedure independently of underlying table permissions)
Read more about Writing Stored Procedure in SQL - Best Practice
Sql Stored Procedure Example
To write a sql stored procedure use the create command:-
create procedure sp_ShowEmpDetails
select Name from Employee
Exec Stored ProcedureTo execute a sql stored procedure use the execute command:-
execute sp_ShowEmpDetails 'meerut'
Drop Stored ProcedureTo delete a sql stored procedure use the drop command:-
drop procedure sp_ShowEmpDetails;
Conclusion: It was fun in learning and writing an article on SQL Stored Procedures. I hope this article will be helpful for enthusiastic peoples who are eager to learn and implement some interesting stuffs in new technology.
Please feel free to comment your opinion about this article or whatever you feel like telling me. Also if you like this article, don't forget to share this article with your friends. Thanks!