Stored Procedure in SQL Server

Introduction

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 pre-compiled 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.

Create Stored Procedure

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

CREATE PROC 

usp_GetAllStudent 

AS BEGIN s

select * from Student 

END

Modify Stored Procedure 

For modifying the 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 output]

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 like.

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 stored procedure and types of stored procedure.

I hope this post will help you. Please put your feedback using comment which helps me to improve myself for next post or send mail to dotnetcircle@gmail.com. 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.