SQL stored procedure is stored in database and it is a collection of SQL command and SQL statements. Stored procedure is help us to create SQL query that are stored and execute on server. We can reused the code inside the stored procedure. Stored procedure is a stored program in which we can pass the parameter. The use of stored procedure is to perform a specific task. When programmer change in the code inside the procedure the application need not to redeploy again.
Syntax for creating a stored procedure :
CREATE PROCEDURE procedure_name ;
AS
Sql_statement;
Execute the stored procedure:
EXEC procedure_name;
Lets have a database table ‘Customer’.
Following the SQL statement create a stored procedure for ‘Customer’ that stores customer id , customer name and city of customer.
create procedure Customerprocedure as
select customer_id, cust_name,city from Customer;
The following SQL statement is used to execute the Customer’s stored procedure.
exec Customerprocedure;
The following SQL statement is to create stored procedure with one parameter.
create procedure CustProcedure @name varchar(256)
as
select * from Customer where cust_name=@name;
To show the created stored procedure which has parameter you follow the following SQL statement.
exec CustProcedure @name='Julian Green';
The following SQL statement is used to create a stored procedure with multiple parameters.
create procedure CustomeProcedure2 @Id int, @Name varchar(256), @City varchar(256)
as
select * from Customer where customer_id=@Id and cust_name=@Name and city=@City;
To execute the above procedure the following SQL statement is used.
exec CustomeProcedure2 @Id=3005, @Name='Graham',@City='California';
Benefits of Stored Procedure that the Programmer is used it in his application :
1- Easily Modified:
We can easily modified the code inside the stored procedure without redeploy or restart the application. If the T-SQL query is written in the application and if we need to change the logic of T-SQL query then we must need to change the code inside the application and also need to redeploy it. So the stored procedure removed it means if we need to change in T-SQL logic then we changed it from stored procedure by using ALTER PROCEDURE statement and need not to redeploy the application.
2- Reusable :
If we create the stored procedure in the SQL Server then it can be used by multiple users or multiple client application at a time. We don’t need to write code again and again.
3- Security :
Stored procedure did not allow to direct access table from database. We can also encrypt the stored procedure when it creates it means all the code inside the procedure are not visible. To see the encrypted code inside the procedure we need to use third party tools like ‘ApexSQL Decrypt’.
4- It reduce network traffic :
If we create stored procedure in our application then where I have need to write the T-SQL code then we pass the procedure name instead of writing whole T-SQL code in all places. By the use of stored procedure the length of code is short and database perform much better.
