Stored Procedure
Stored procedure is a SQL statement which is precompiled and stored on the database server. A stored procedure can be created on the frequently used SQL script and saved on the database server with a name. The next time when that SQL script is needed to be executed, we just need to call that stored procedure. The stored procedure allows input and output parameters, so the same procedure can be used by different clients over the network. A stored procedure can be written for any DML, DCL, and DDL command. Exceptions can be handled using the try...catch block in the stored procedure. A stored procedure can be executed using the Execute or exec keyword.
Syntax
- EXECUTE sp_columns 'Employee'
In the above syntax, the EXEC keyword is used to execute the procedure. sp_columns is the procedure name and ‘Employee’ is the parameter.
There are two types of stored procedure.
- System stored procedure
- User-defined stored procedure
System Stored Procedure
System store procedure is provided by SQL Server to run and maintain SQL Server database and provide information about the database and database objects. System procedure name starts from sp_.
User-Defined Stored Procedure
A user-defined stored procedure is created by the users, like other database objects. A stored procedure can be created with the parameter or without parameter. A user-defined stored procedure is created by using the CREATE PROCEDURE statement.
Syntax to create stored procedure,
- CREATE PROCEDURE getEmployee
- AS
- BEGIN
- SELECT Id, FirstName, LastName, DateOfBirth, Email
- FROM Employee;
- END;
With parameter,
- CREATE PROCEDURE getEmployee
- @Id INT
- AS
- BEGIN
- SELECT Id, FirstName, LastName, DateOfBirth, Email
- FROM Employee WHERE Id = @Id
- END;
The procedure can be dropped using the drop proc command.
Syntax
- DROP PROCEDURE getEmployee;
All the stored procedures can be found in the database inside the Programmability >> Stored Procedures directory.
Or user-defined procedure can be found in the system’s procedure view using the below select statement,
- SELECT * FROM sys.procedures;
Advantages Of Stored Procedure
There are many advantages of using stored procedure Performance.
Performance
Stored procedure compiles once when it is created and saved on the database server and does not need recompile when it is called. When Stored procedure executes, it creates an execution plan and saves that to reuse. When it is called again, it reuses the execution plan.
The same SP can be used by the different clients over the network. There is no need to send the whole SQL script to work for data. The SQL script can be saved as a stored procedure with a name and client or the application only needs to send the procedure name to execute it. It saves a lot of bandwidth.
Security: Stored procedures provide better security of data and also help to avoid SQL injection attacks.
Execution of the stored procedure is faster than dynamic SQL as the stored procedure uses an execution plan. An execution plan isn’t created for dynamic SQL. Stored procedure reduces the network traffic as it requires only procedure name to pass and provides better security as SQL script resides on the database server. The SQL logic does not come from the network when it is called.