/*create procedure sp_getdept_emp
as begin

select * from tbldept;

select * from tblemp;

end
*/

/*
Calling
USE [sircldb]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[sp_getdept_emp]

SELECT ‘Return Value’ = @return_value

GO
*/
exec sp_getdept_emp;

/*
Function | Procedure :-
1. Just call me and use me.
syntax: exec proc_name;
2. proc said if i required some argument then caller need to pass those arguments
IN – call by value (default)
OUT – call by ref
*/
/*Create Procedure spGetEmployeeCountByDept
@dno nvarchar(20),
@empcount int Output
as
Begin
Select @empcount = COUNT(ENo)
from tblemp
where DNo = @dno
End */ declare @ec int; exec spGetEmployeeCountByDept 10,@ec output; select @ec; create procedure sp_sum(@n1 int, @n2 int) as begin declare @r int; set @r=@n1+@n2; select @r; end exec sp_sum 10,20 ;