Stored Procedure with cases and nested queries

Stored Procedure Sample

USE [DBName]
GO
/****** Object:  StoredProcedure [dbo].[GetTabsContentDetails]    Script Date: 07/31/2018 02:40:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Exec [GetTabsContentDetails] '4082' 

ALTER PROCEDURE [dbo].[GetTabsContentDetails] 
   @projectId nvarchar(50) 
   
AS 
  -- Titles, desc ------------
SELECT
CASE WHEN LEN(CONVERT(nvarchar(50), PROJECT_NO)) >=5 THEN
CASE WHEN LEN(CONVERT(nvarchar(50), INCREMENT_NO)) >=3 THEN
(CONVERT(nvarchar(50),PROJECT_NO) +'-' +CONVERT(nvarchar(50),INCREMENT_NO)+CONVERT(nvarchar(50),AMENDMENT_NO))
ELSE
(CONVERT(nvarchar(50),PROJECT_NO) +'-' +RIGHT('000' + CONVERT(nvarchar(50), INCREMENT_NO), 3)+CONVERT(nvarchar(50),AMENDMENT_NO))
END
        ELSE
CASE WHEN LEN(CONVERT(nvarchar(50), INCREMENT_NO)) >=3 THEN
(RIGHT('00000' + CONVERT(nvarchar(50), PROJECT_NO), 5) +'-' +CONVERT(nvarchar(50),INCREMENT_NO)+CONVERT(nvarchar(50),AMENDMENT_NO))
ELSE
(RIGHT('00000' + CONVERT(nvarchar(50), PROJECT_NO), 5) +'-' +RIGHT('000' + CONVERT(nvarchar(50), INCREMENT_NO), 3)+CONVERT(nvarchar(50),AMENDMENT_NO))
END
END
as ECONo,PROJECT_NAME As Title, DESCRIPTION as Description
FROM dbo.Project where PROJECTID= @projectId --7596
  -- End ---
 
  -- Dates Tab    ------------
-- SELECT planned_start_date, planned_end_date, actual_start_date, actual_end_date, engdate.CATDATE as Engineer_Date,
-- SIGNOFFREQUEST_DATE, (last_name+ ' ' +first_name) as Manager FROM project, employee, dbo.CAT_ENGINEER_DATES engdate
--  WHERE creatorid = empid AND project.engdateid = engdate.CATID AND projectid = @projectId
-- CALLING STORED PROCEDURE F0R ENG DATE
  EXEC GetEngDate @projectId
    --End Dates--
 
  -- option Tab  -------------
SELECT MTH.CAT_DESCRIPTION as Methodology, PR.CAT_DESCRIPTION as [Priority], CE.CAT_DESCRIPTION as [Copy Exact]
FROM PROJECT PRJ, dbo.CAT_METHODS MTH, dbo.Cat_priority PR, dbo.CAT_COPY_EXACT CE
WHERE MTH.CATID = PRJ.METHODID AND PR.CATID = PRJ.PRIORITYID AND CE.CATID=PRJ.INTELCOPYID AND PRJ.PROJECTID= @projectId

   
     --End option --
 
  -- Effectivities -----------
 
  SELECT cat_effectivity.cat_description as Effectivity, machine.machine_no as Machine, cat_machine_type.cat_description AS [Type],
  customer.companyname as Customer, customer.site as [Site], cat_country.cat_description AS Country, cat_ship_date.catdate as [Ship Date], cat_effectivity_reason.cat_description AS Reason FROM
  MAC_PROJ_EFFECTIVITY_ACT maceff, cat_effectivity, machine, cat_machine_type, customer, cat_country, cat_ship_date, cat_effectivity_reason WHERE
  maceff.macid = machine.macid AND maceff.effectivityid = cat_effectivity.catid AND machine.typeid = cat_machine_type.catid  AND machine.customerid = customer.customerid
  AND customer.countryid = cat_country.catid
  AND maceff.effreasonid = cat_effectivity_reason.catid
  AND machine.shipdateid = cat_ship_date.catid AND maceff.projectid=@projectId order by cat_ship_date.catdate desc
 
    --End Effectivities--
 
  -- Documents Tab------------
          SELECT doc_name as [Document Name], (employee.last_name+' '+ employee.first_name) AS [Owner],docaddtime as [Created Date],
          cat_doc_types.full_description as [Document Type], DOC_FILENAME as [Filename],LINEITEMS FROM proj_docs, employee, cat_doc_types
          WHERE proj_docs.employeeid = employee.empid AND proj_docs.doctype = cat_doc_types.catid AND proj_docs.projectid = @projectId
          order by doc_name desc
    --End Doc----
       
  -- SignOff Tab -------------
--SELECT (emp1.last_name+' , '+ emp1.first_name) AS Employee, eco_group.ecogroup_name As [Eco Group],   signatory As Signatory, signoff_date as [Signoff Date],
-- (emp2.last_name+' , '+ emp2.first_name) AS [Actual Signatory] FROM proj_signoff, eco_group, employee emp1, dbo.EMployee_2 emp2
-- WHERE proj_signoff.employeeid = emp1.empid AND proj_signoff.ecogroupid = eco_group.ecogroupid
-- AND emp2.EMPID=proj_signoff.ACTUAL_SIGNOFFID AND proj_signoff.projectid = @projectId order by emp1.last_name asc

-- SELECT (emp.last_name+' , '+ emp.first_name) AS Employee, eco_group.ecogroup_name As [Eco Group],   signatory As Signatory, signoff_date as [Signoff Date],
--   (emp.last_name+' , '+ emp.first_name) AS [Actual Signatory] FROM proj_signoff, eco_group, employee emp where  proj_signoff.ecogroupid = eco_group.ecogroupid
--And proj_signoff.employeeid = emp.empid AND proj_signoff.projectid = @projectId

SELECT (emp1.last_name+' , '+ ISNULL(emp1.FIRST_NAME,'')) AS Employee, eco_group.ecogroup_name As [Eco Group],   signatory As Signatory, signoff_date as [Signoff Date],
  (emp2.last_name+' , '+ ISNULL(emp2.FIRST_NAME,'')) AS [Actual Signatory] FROM proj_signoff, eco_group, employee emp1, dbo.EMployee_2 emp2
  WHERE proj_signoff.employeeid = emp1.empid AND proj_signoff.ecogroupid = eco_group.ecogroupid
  AND emp2.EMPID=proj_signoff.ACTUAL_SIGNOFFID AND proj_signoff.projectid = @projectId
      UNION ALL
SELECT (emp.last_name+' , '+ ISNULL(emp.FIRST_NAME,'')) AS Employee, eco_group.ecogroup_name As [Eco Group],   signatory As Signatory, signoff_date as [Signoff Date],
    (emp.last_name+' , '+ ISNULL(emp.FIRST_NAME,'')) AS [Actual Signatory] FROM proj_signoff, eco_group, employee emp where  proj_signoff.ecogroupid = eco_group.ecogroupid
And proj_signoff.employeeid = emp.empid AND proj_signoff.projectid = @projectId and SIGNOFF_DATE is null order by Employee asc

    --End Signoff --

   -- Costs Tab ---------------
         -- For Cost delta------
    SELECT CC.CAT_DESCRIPTION as Category , PC.ESTIMATED as Forecast,PC.ACTUAL as Actual FROM PROJ_COST_DELTA PC, dbo.CAT_COST_DELTA CC
where CC.CATID=PC.CATID and pc.PROJECTID=@projectId
         --End ----
         -- For Cost implementation------
    SELECT CC.CAT_DESCRIPTION as Category ,PC.ESTIMATED as Forecast,PC.ACTUAL as Actual FROM dbo.PROJ_COST_IMP PC,  dbo.CAT_COST_IMP CC
where CC.CATID=PC.CATID and pc.PROJECTID=@projectId
         --End ----
         --For Manhours----
    SELECT CM.CAT_DESCRIPTION as Category, PM.ESTIMATED as Forecast, PM.ACTUAL as Actual FROM PROJ_MANHOURS PM, dbo.CAT_MANHOURS CM
WHERE PM.CATID=CM.CATID AND PM.PROJECTID=@projectId
    --End cost-------
   
    -- Reason for Option table ----
         select CD.CAT_DESCRIPTION as Reason from proj_reason PR, dbo.CAT_PROJECT_REASON CD where PR.PROJREASID=CD.CATID AND projectid = @projectId
    --End--
   
   --Phase table----
  select distinct ph.CAT_DESCRIPTION as Phase, PHASE_TITLE as [Title], PLANNED_START_DATE as [Planned StartDate],
  ACTUAL_START_DATE as [Actual StartDate], PLANNED_END_DATE as [Planned EndDate],
  ACTUAL_END_DATE as [Actual EndDate] from dbo.Proj_phas p, dbo.CAT_PHASE ph
          where p.PHASEID=ph.CATID  AND PROJECTID = @projectId
   ---end-----------