Introduction
In this article we are going to discuss Stored Procedures.
A Stored Procedure is just like a procedure or function in Programming. We can encapsulate some useful code into a procedure, and can call it whenever we need.
A Stored Procedures could also have some parameters, which accepts input and return multiple values as output. To learn more about Stored Procedures you can visit the link below:
http://msdn.microsoft.com/en-us/library/ms191436%28SQL.105%29.aspx
Sometimes we need to run the same query multiple times, but we hesitate doing it again and again. Stored Procedure solves this problem by creating a normal function or procedure. We are creating a procedure which selects data of those employees, who have a job title as "engineers".
Code Sample of Stored Procedure
Here is the code of a simple Stored Procedure.
use [DatabaseName]
Go
create Procedure Procedure_Name
As
select * from employee where jobTitle='engineers'
Go
The above code would generate a stored procedure which could select all data of employees who are engineers. We could set update, delete, insert, or even a join in the query.
In order to run this procedure we use the "execute" command with the "procedure name"; we could also use the "exec" keyword with a procedure name to execute it.
execute Procedure_Name
exec Procedure_Name
Passing Values to Parameters
To Pass parameters to Stored Procedures use the following code :
use [DatabaseName]
Go
create Procedure StoredProcedure_Name
@var varchar(20)
As
select * from table_name where name = @var
Go
Here we have declared a variable using '@var' by specifying its datatype as well. You can create any variable of any datatype but don't forget to put the '@' symbol before declaring a variable.
When executing the above procedure, we have to provide the value of each parameter. The parameters could be more than one, while executing, we need to provide the value of each parameter .
execute StoredProcedure_Name @var='value'
This will execute the above stored procedure and fetch all of the information of that person whose name you provided.
Here is another example of passing parameters to a stored procedure. This procedure will input six different parameters when executing. Every time we run this procedure we can insert multiple values with the same stored procedure.
use [DatabaseName]
Go
create Proc StoredProcedure_Name
@ID int(5),
@name varchar(30),
@job varchar(10),
@contact varchar(11),
@roll varchar(4),
@result varchar(6)
As
insert into table_name values(@ID,@name,@job,@contact,@roll,@result)
Go
exec StoredProcedure_Name
@ID='00001',
@name='ABC',
@job='Engineer',
@contact='1234567',
@roll='33',
@result='Passes'
We can also replace the keyword 'Procedure' as 'Proc' while creating a Stored Procedure as we did in the above procedure.
Conclusion
You can save multiple queries in your stored procedure once, and use it multiple times whenever you want.