Hi:
-- Very Narrow (all IDs are passed in)
IF(@EmpID IS NOT NULL AND @DeptID IS NOT NULL AND @CityID IS NOT NULL)
BEGIN
SELECT
e.EmpName
,d.DeptName
,c.CityName
FROM
Employee e WITH (NOLOCK)
JOIN Department d WITH (NOLOCK) ON e.deptid = d.deptid
JOIN City c WITH (NOLOCK) ON e.CityID = c.CityID
WHERE
e.EmpID = @EmpID
END
-- Just 2 IDs passed in
ELSE IF(@DeptID IS NOT NULL AND @CityID IS NOT NULL)
BEGIN
SELECT
e.EmpName
,d.DeptName
,NULL AS [CityName]
FROM
Employee e WITH (NOLOCK)
JOIN Department d WITH (NOLOCK) ON e.deptid = d.deptid
JOIN City c WITH (NOLOCK) ON e.CityID = c.CityID
WHERE
d.deptID = @DeptID
END
-- Very Broad (just 1 ID passed in)
ELSE IF(@CityID IS NOT NULL)
BEGIN
SELECT
e.EmpName
,NULL AS [DeptName]
,NULL AS [CityName]
FROM
Employee e WITH (NOLOCK)
JOIN Department d WITH (NOLOCK) ON e.deptid = d.deptid
JOIN City c WITH (NOLOCK) ON e.CityID = c.CityID
WHERE
c.CityID = @CityID
END
-- None (Nothing passed in)
ELSE
BEGIN
SELECT
NULL AS [EmpName]
,NULL AS [DeptName]
,NULL AS [CityName]
END
Question: Is there any better way (OR specifically can I do anything without IF...ELSE condition?
