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