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
