CREATE PROC GetData
@Type varchar(30)
AS
IF @Type='All'
BEGIN
SELECT s.Name,
sl1.Name,
CASE WHEN sl1.Number < sl.Number THEN '<'
     WHEN sl1.Number > sl.Number THEN '>'
     ELSE '='
END AS Condition,
sl.Name
FROM Employee_Skill es
INNER JOIN Skill s
ON s.Number=es.Skill_NBR
INNER JOIN Title_Skill ts
ON ts.Skill_NBR=s.Number
INNER JOIN Skill_level sl
ON sl.Number=ts.Skill_level_NBR
INNER JOIN Skill_level sl1
ON sl1.Number=es.rating
INNER JOIN  Title t
ON t.Number=ts.Title_NBR
INNER JOIN EMployee e
ON es.Employee_nbr=e.Number
WHERE e.Name='Jhon'
AND t.Name='DBA'
END
IF @Type='difference'
BEGIN
SELECT s.Name,
sl1.Name,
CASE WHEN sl1.Number < sl.Number THEN '<'
     WHEN sl1.Number > sl.Number THEN '>'
     ELSE '='
END AS Condition,
sl.Name
FROM Employee_Skill es
INNER JOIN Skill s
ON s.Number=es.Skill_NBR
INNER JOIN Title_Skill ts
ON ts.Skill_NBR=s.Number
INNER JOIN Skill_level sl
ON sl.Number=ts.Skill_level_NBR
INNER JOIN Skill_level sl1
ON sl1.Number=es.rating
INNER JOIN  Title t
ON t.Number=ts.Title_NBR
INNER JOIN EMployee e
ON es.Employee_nbr=e.Number
WHERE e.Name='Jhon'
AND t.Name='DBA'
AND sl.Number <> sl1.Number
END
IF @Type='deficiency'
BEGIN
SELECT s.Name,
sl1.Name,
CASE WHEN sl1.Number < sl.Number THEN '<'
     WHEN sl1.Number > sl.Number THEN '>'
     ELSE '='
END AS Condition,
sl.Name
FROM Employee_Skill es
INNER JOIN Skill s
ON s.Number=es.Skill_NBR
INNER JOIN Title_Skill ts
ON ts.Skill_NBR=s.Number
INNER JOIN Skill_level sl
ON sl.Number=ts.Skill_level_NBR
INNER JOIN Skill_level sl1
ON sl1.Number=es.rating
INNER JOIN  Title t
ON t.Number=ts.Title_NBR
INNER JOIN EMployee e
ON es.Employee_nbr=e.Number
WHERE e.Name='Jhon'
AND t.Name='DBA'
AND sl.Number < sl1.Number
END
GO

DECLARE @EmployeeName VARCHAR(MAX)
DECLARE @TitleName VARCHAR(MAX)
DECLARE @Filter INT

SELECT @EmployeeName = 'Jhon',
		@TitleName = 'SQL DBA'
		
SELECT @Filter = 1	-- Show ALl
--				2	-- Show Difference
--				3	-- Show Deficiencies

SELECT	E.Skill, E.Level AS 'EmpCompetency',
		CASE	WHEN E.LevelNo < T.LevelNo THEN '<'
				WHEN E.LevelNo = T.LevelNo THEN '='
				ELSE '>' END AS 'Condition',
		T.Level AS 'Required competency'
FROM (
		SELECT ES.Skill_Nbr, S.Name 'Skill', ES.Employee_Nbr, E.Name AS 'Emp', SL.Name 'Level', SL.Number 'LevelNo'
		FROM @Employee_skill ES
		INNER JOIN @Skill S ON S.NUmber = ES.Skill_Nbr
		INNER JOIN @Employee E ON E.Number = ES.Employee_Nbr
		INNER JOIN @Skill_level SL ON SL.Number = ES.supervisorrating
		WHERE E.Name = @EmployeeName
	) E
INNER JOIN 
	(
		SELECT TS.Skill_Nbr, S.Name 'Skill', TS.Title_nbr, T.Name AS 'Title', SL.Name 'Level', SL.Number 'LevelNo'
		FROM @Title_Skill TS
		INNER JOIN @Skill S ON S.NUmber = TS.Skill_Nbr
		INNER JOIN @Title T ON T.Number = TS.Title_nbr
		INNER JOIN @Skill_level SL ON SL.Number = TS.Skill_level_nbr
		WHERE T.Name = @TitleName
	) T ON T.Skill_Nbr = E.Skill_Nbr
WHERE ( @Filter = 1
		OR ( @Filter = 2 AND E.LevelNo <> T.LevelNo )
		OR ( @Filter = 3 AND E.LevelNo < T.LevelNo ))

"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"