Sql Stored Procedure Tutorial with Examples

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.

sql stored procedure tutorial with examples

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.

OR

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)

So, SQL Server supplies stored procedures for managing SQL Server and displaying information about database and users.

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 SQL Stored Procedure Best Practices

Sql Stored Procedure Example

To write a sql stored procedure use the create command:-

create procedure sp_ShowEmpDetails

@city varchar(20)

as

Begin

select Name from Employee

where City=@city

End

Go

Exec Stored Procedure

To execute a sql stored procedure use the execute command:-

execute sp_ShowEmpDetails 'meerut'

Drop Stored Procedure

To delete a sql stored procedure use the drop command:-

drop procedure sp_ShowEmpDetails;

Conclusion: It was fun in learning and writing an article on stored procedure example in sql server 2005. 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!