SQLTeam.com | Weblogs | Forums

How to get data from employee table


#1

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'


#2

For this one, you'll need to check whether the previous SELECT returned any data. You can use @@ROWCOUNT for that.