id name salary desg datejoin isactive user mgr_id
1 Joe 2000.00 software developer 2010-11-01 1 green 2
2 brown 10000.00 jr assist 2008-10-05 1 manu NULL
3 brown 15000.00 team leader 2014-05-15 0 anu 2
4 harry 20000.00 hr manager 2011-09-25 1 anji 1
5 jenny 30000.00 accountist 2010-11-01 1 hash 4
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getemployees')
BEGIN
DROP PROCEDURE sp_getemployees ;
END
GO
CREATE PROCEDURE sp_getemployees
(
@mgr_id int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @managerid int
IF @mgr_id IS NULL
BEGIN
SELECT * FROM employees
END
IF @mgr_id is not null
BEGIN
select * from employees where mng_id= @mgr_id
END
ELSE
RAISERROR (' no data found fro given manager id', -- Message text.
16, -- Severity.
1 -- State.
);
END
query like follows
if mgr_id is null get all records
if mgr_id is there in table employee get perticular records
if mgr_id is not in table data display 'no data found'