Dynamic Query - SQL Server

Berikut adalah contoh dari bentuk dari Dynamic Query di SQL Server yang dibungkus ke dalam Store Procedure: 

USE [Data00]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uSP_RPT_SUB_NEWTotal_Tunned] (@branch_cd nchar(2), @Cons_Leas nchar(1)
, @date smalldatetime, @curr nchar(3)
) as
 DECLARE @sqlCommand varchar(max),@sqland1  varchar(8000), @sqland2  varchar(8000), @sqland3  varchar(8000)
declare @admfeeTable as table(
contract_no varchar(10),
customer_name varchar(100),
[date] smalldatetime,
bagin_balance money,
Additional money,
Accrual1 money,
ACCRUAL2 money,
END_BAL money,
Amortitation money,
UPD_STAT char(1),
ET_FC  money,
Branch_cd varchar(2),
[type] varchar(1),
tahun int,
BRANCH_NAME varchar(30),
Sales_no varchar(10),
ACC varchar(1),
SD_Type varchar(10),
ET_DT smalldatetime
) 
;
SET @sqlCommand = '
select 
   CC.LEASE_NO as contract_no,
    CL.LESSEE_NM as customer_name,
    AN.PERIOD as date,
   AN.Begbal as bagin_balance ,
   AN.Additional,
   AN.Accrual1,
    case when convert(varchar(6),ET_DT,112) = '''+CONVERT(VARCHAR(6),@date,112)+''' then 0
else AN.ACCRUAL2 end as ACCRUAL2, 
    case when convert(varchar(6),ET_DT,112) = '''+CONVERT(VARCHAR(6),@date,112)+''' then 0
else AN.ENDBAL end as END_BAL,
   case when convert(varchar(6),ET_DT,112) = '''+CONVERT(VARCHAR(6),@date,112)+''' then AN.Accrual1 + 0
else AN.Accrual1 + AN.ACCRUAL2 end as Amortitation, CC.UPD_STAT ,
    case when CC.UPD_STAT=''T'' and convert(varchar(6),ET_DT,112)='''+CONVERT(VARCHAR(6),@date,112)+''' then
   AN.ET_FC
   when CC.UPD_STAT=''Y''  then
   0
   else
   0
    end as ET_FC ,
   AN.Branch_cd  ,
   CC.Cons_Leas as type,
   case when (month(collection) = 12 and month(execution) = 1 and ADV_ARR = ''V'') or (month(collection) = 1 and month(execution) = 1 and ADV_ARR = ''R'') then year(execution) - 1
   else year(con_date) end as tahun,
   BRANCH_NAME,AN.Sales_no,AN.ACC,an.SD_Type,ET_DT

    from ACPREAMOR_PSAK AN WITH(NOLOCK)
   inner join CLFCONT CC WITH(NOLOCK)  on AN.LEASE_NO = CC.LEASE_NO
   left join CLFLESS CL WITH(NOLOCK)  on CL.LESSEE_NO = CC.LESSEE_NO
   left join CLFPAY CP WITH(NOLOCK)  on AN.LEASE_NO = CP.LEASE_NO and AN.PERIOD =CP.DATE_DUE  and CP.PERIOD > 0
   left join CLFATR CA WITH(NOLOCK)  on AN.LEASE_NO = CA.LEASE_NO
   left join UMFBRANCH UB WITH(NOLOCK)  on UB.BRANCH_CD=AN.Branch_cd
   where
   (convert(varchar(6),CC.ET_DT,112)>='''+CONVERT(VARCHAR(6),@date,112)+''' or  CC.ET_DT is null)
   and AN.ACC = ''S''   
   AND CC.CURR_CODE='''+@curr+'''
   and convert(varchar(6),an.Period,112)='''+convert(varchar(6),@date,112)+''''
  
SET @sqland1 = 'and CC.BRANCH_CD = '''+@branch_cd+''''
SET @sqland2 = 'and CC.Cons_Leas='''+@Cons_Leas+''''

if @branch_cd ='00'
BEGIN 
 IF @Cons_Leas = 'A'
BEGIN
SET @sqlCommand = @sqlCommand
END
ELSE
BEGIN
    SET @sqlCommand = @sqlCommand + @sqland2
END
END
ELSE
BEGIN
IF @Cons_Leas = 'A'
BEGIN
SET @sqlCommand = @sqlCommand + @sqland1
END
ELSE
BEGIN
    SET @sqlCommand = @sqlCommand + @sqland1 + @sqland2
END
end
INSERT INTO @admfeeTable
exec (@sqlCommand)
select adm.tahun, sum(ISNULL(adm.END_BAL,0)) as END_BAL from (select distinct * from @admfeeTable) adm group by adm.tahun