What Are the Advantages of Using Stored Procedures in SQL Server

Today, I am going to explain, what is stored procedure in SQL and advantage of stored procedure in SQL and something more about stored procedure how to create stored. So, I am trying to explain stored procedure with example.

What is stored procedure?

Stored Procedure is a group of precompiled Transact SQL statement into a single execution plan. It reduces the network traffic and increase the performance because of the commands in stored procedure is executed as single batch of code

Stored procedure can be reuse in the application. It contains programming statement that performs operation in the database. It stored in database in physical location inside stored procedure folder of progreammability.

Create Stored Procedure

To create a new stored procedure use “Create procedure” or “Create Proc” command.

CREATE PROC usp_GetAllStudent
AS
BEGIN
select * from Student
END

Modify Stored Procedure

For modifying the stored procedure , use Alter command to modify the existing stored procedure.

ALTER PROC usp_GetAllStudent
AS
BEGIN
select * from Student
END

Delete Stored Procedure

If there is requirement to delete the stored procedure, simply drop it using Drop command.

drop proc usp_GetAllStudent

Execute Stored Procedure

Exec or Execute command is used for executing the stored procedure

EXEC usp_GetAllStudent

Stored Procedure with parameter [input or outpu]

If there is requirement to create stored procedure based on condition then you can create a stored procedure which takes parameters. Parameters can be input or output. Stored procedure takes input and output both type of parameters.

Example for Input Parameter

CREATE PROC usp_GetStudentById
@StudentId int
AS
BEGIN
select * from Student where StudentId=@StudentId;
END

Execute like this

EXECUTE usp_GetStudentById 1

Example for Output prameter

CREATE PROC usp_GetStudentAddressByID
@StudentId int,
@Address varchar(255) OUTPUT
AS
BEGIN
SELECT @Address=Address FROM Student WHERE StudentId=@StudentId;
END

Execute like this

DECLARE @Address varchar(255)
EXEC usp_GetStudentAddressByID 1, @Address OUTPUT
SELECT @Address AS StudentAddress

Advantage of stored procedure

There are so many advantage to use stored procedure rather than a simple query.

Reduce network traffic

Stored procedures are precompiled so there is no need to compile every time. It reduces the network traffic because of it executes in single execution plan. Stored procedure is also cached on the server.

Security

When we call a stored procedure over the network, we can execute stored procedure based on your parameter but we can see the table and database object name.

Maintainability

Stored procedure scripts are in one location so updating and tracking of dependencies based on schema changes becomes easier. Stored procedure can be tested independent of the application

Types of stored procedures

User defined Stored Procedure

User defined stored procedure can be created in user defined database or any other database except the resource database.

System defined Stored Procedure

System defined stored procedure is physically stored into hidden Resource database and logically we can find in sys schema of every system. Msdb database also contain system define stored procedure in dbo schema.  It basically starts with sp_ prefix.

Temporary Stored Procedure

Temporary stored procedure is also type of user defined stored procedure. It stored in tempdb. As like temporary table it is also two type Local temporary stored procedure and Global temporary stored procedure.

Remote Stored Procedure

Remote stored procedure basically created and stored on remote server database. and these remote procedure can be accessed from other server.

NOTE:

Don't use sp_ prefix to create stored procedure. It is a standard naming convention that is used in master database.  If you use sp_ , Sql server will first search stored procedure in master database and after that it will search user database. So, avoid it. you can use usp_ or other prefix as you lik.

Conclusion

So, Here We learn what is stored procedure and how we can create, drop, update and execute stored procedure. and we also learned advantage of storeed procedure and types of stored procedure.

Reference

If you want to know more please click here. http://msdn.microsoft.com/en-us/library/ms190782.aspx

I hope this post will help you. Please put your feedback using comment which helps me to improve myself for next post. If you have any doubts please ask your doubts or query in the comment section and If you like this post, please share it with your friends. Thanks.