Stored Procedures are a set of SQL Commands / statements which are stored in a compiled form.
Syntax:
CREATE/ALTER Procedure <Procedure-name>
(
Input parameters ,
Output Parameters (If required)
)
AS
BEGIN
-- SQL Statements used in the stored procedure
END
Why Should we use Stored Procedures ? / What are the Benefits of Stored Procedures ?
- Increased Performance – Stored Procedure is in compiled form. So need of compilation everytime they called.
- Reusability – You can write Stored Procedure once, compile it and it can be used by multiple applications or users possessing necessary permissions.
- As compared to normal SQL Statements using Stored Procedures is a good practice, it not only save compilation time everytime it is called but gives you error free approach.
- They are More Secure. Because while calling the procedure over the network only the execute statement is visible, Thereby malicious users cannot see the table and database objects or the T-SQL statements used.
- By using Parameters in Procedures we can save our DB from SQL injection attacks. Because they are not treated as executable code but as literals so it is very difficult for the attackers to insert any command in the Procedure.
- It is good practice to use TRY…CATCH Block in the Stored Procedures.
- You can use Functions inside Procedure.
Features of Stored Procedures
- They may or may not return a value.
- They can use SELECT as well as DML statements like INSERT, DELETE, UPDATE etc. unlike Functions.
- You can call Functions inside Stored Procedures.
- It also allow us to use Transactions.
- Stored Procedure can have input as well as output parameters.
TIP :
– Procedure cannnot be used with JOINS.
– They cannot be called from SELECT/HAVING/WHERE clause. You can use EXECUTE or EXEC statements.
Lets take a look on some examples.
Stored Procedure with input parameters
You can see that the above procedure is taking 3 parameters and doing insertion in the employee table. So at the time you execute the procedure you have to provide it with 3 required parameters.
EXEC sp_insert 10,'Aarvi','Himachal'
You can use EXEC or EXECUTE.
Create Procedure with output parameters
CREATE procedure sp_out
(
@roll int,
@name varchar(20) out --out keyword is used to specify for output parameter
)
as
begin
select @name= fname+' '+lname from student where roll=@roll
end
In the above code, we have created a PROCEDURE which takes ‘roll’ as input parameter and do processing accordingly and gives out the fullname as output. See that the output parameter is declared with ‘out’ keyword.
Execution of above code
DECLARE @name as varchar(20)
exec sp_out 2, @name output
select @name as name
Explaination – According to the above procedure , if you have a table with 2 columns ‘fname’ and ‘lname’, storing data as fname -> Arvind and lname ->Dharwal. The output ‘name’ will be the Arvind Dharwal.