STORED PROCEDURE :¶

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

How to create stored procedure :¶

create procedure procedurename 
as
begin 

----sql statements
end

How to alter stored procedure :¶

alter procedure procedurename
as
begin 

----sql statements
end

How to drop a stored procedure :¶

1	`drop procedure procedure`

STORED PROCEDURE WITH INPUT PARAMETERS¶

create procedure procedurename(@empname)
as
begin
select * from emp where empname=@empname
end

STORED PROCEDURE WITH OUTPUT PARAMETER :¶

To create STORED PROCEDURE with output parameter we use the keyword OUT or OUTPUT. for example : employeecount is an output notice it is specified with OUTPUT keyword.

create procedure SpGetEmployeeCountByJob(
    @Job varchar(50),
    @EmployeeCount int output
)
as
begin
select @EmployeeCount = Count(id) from Emp where Job=@Job 
end

To execute this STORED PROCEDURE 1.Initialise a variale of the same datatype as output parameter, we have declared @newresult int 2.then pass the @newresult variable to the stored procedure ,you have to specify output keyword otherwise the variable will be null 3.execute

declare @newresult int
SpGetEmployeeCountByJob @job='salesman', @EmployeeCount=@newresult out
select @newresult