ALTER procedure [dbo].[sp_casedetailsreport](@casenumber varchar(50)) as begin declare @layweid varchar(20) declare @lawyername varchar(50) declare @clienttype varchar(50) select @layweid=Current_Lawyer_Id from CMS.dbo.Client_Lawyer_Modification_details where Case_Number=@casenumber select @lawyername=Current_Lawyer_Name from CMS.dbo.OpponentLawyer_Modification_details where Case_Number=@casenumber select cad.Date AS RegistrationDate,cad.Case_Number as CaseNumber,cad.Court_Hall as CourtHall,cad.Case_Type as CaseType,cad.Client_Type as ClientType,cld.Client_Name as ClientName,cld.Client_PhoneNumber as ClientPhoneNumber,cop.Opp_Name as ClientOpponentName,cop.Opp_PhoneNumber as ClientOpponentPhoneNumber ,ld.Lawyer_Name as LawyerName,olmd.Current_Lawyer_Name as OpponentLawyerName,fud.Current_Details as CurrentDetails,fud.Current_Status as CurrentStage,fud.Next_Hearing_Date as NextHearingDate from CMS.dbo.Case_details cad inner join CMS.dbo.Client_details cld on cld.Case_Number=cad.Case_Number and cld.Client_Number='P1' inner join cms.dbo.Client_Opponent_details cop on cop.Case_Number=cad.Case_Number and cop.Opp_Number='O1' inner join CMS.dbo.Client_Lawyer_Modification_details clmd on clmd.Case_Number=cad.Case_Number and clmd.Current_Lawyer_Id=@layweid inner join CMS.dbo.OpponentLawyer_Modification_details olmd on olmd.Case_Number=cad.Case_Number and olmd.Current_Lawyer_Name=@lawyername inner join CMS.dbo.Lawyer_Details ld on ld.Lawyer_Id=clmd.Current_Lawyer_Id inner join CMS.dbo.Follow_up_details fud on fud.Case_Number=cad.Case_Number and fud.Date=(select MAX(Date) from CMS.dbo.Follow_up_details) where cad.Case_Number=@casenumber end