Stored Procedure in Sql Server

what is a stored procedure ?
 A stored procedure is group of T-SQL statements. whenever we want write  a same query over and over again,we can save the specific query as  a stored procedure and call just by its name.

Lets take a simple  example 

we have table named employee .we want to get name and department of employee every time .

for that we need to write a query and run every time

query :select name, department from employee 

Instead of  writing the query every time, we can wrap up this query inside stored procedure and call that .
To create a stored procedure

create procedure getemployeedtls
as 
begin 

select name, department from employee
end 



and execute the stored procedure by using its name  getemployeedtls or else 

 execute getemployeedtls

we can execute the stored procedure whenever we want the details of employee table no need to run the query.

Actually stored procedure has many uses

In reality the stored procedure will be long ,there are queries 2000 to 3000 lines.it is very difficult to run query every time . by using the stored procedure we can speed up the process.
  It will reduce the network traffic .
security also main aspect of stored procedure.

Stored procedure with parameters


create procedure employeeiddept
@department varchar(20)
as 
begin 
select name ,salary,department from employee where department=@department
end 

to execute the above procedure we should pass parameter

 employeeiddept  'finance'

we get all the names and salary of employee table belongs to finance department.

We can alter the stored procedure by using the alter command

For example we want the result order by salary

Alter  procedure employeeiddept
@department varchar(20)
as 
begin 
select name ,salary,department from employee where department=@department order by salary
end