Stored Procedures :

• Named blocks in TSQL.
• Also called sub-programs because they are called in the main program.
• It is pre-compiled named block stored in database that performs a task and returns 0 to n output values.

Syntax:

:

Ex 1:- write a procedure to add two numbers.

create procedure stored_sum
@x int, @y int, @z int output
as 
begin
set @z=@x+@y
end

Execute the stored procedure:

declare @a int , @b int, @c int
set @a=3
set @b=3
execute stored_sum @a,@b,@c output
print @c

Ex 2:- write a procedure to increment the  given employee salary by 10%

create procedure increment_employee
@eno int
as
begin
select esal from employ where eid=@eno
update employ set esal=esal+esal*0.1 
select esal from employ where eid=@eno
end

Execute the stored procedure:

exec increment_employee 112