带有可选偏移量查询的java存储过程
我正在尝试编写一个存储过程来获取数据,存储过程如下-
ALTER PROCEDURE SPProfileScholarshipReports
@scholarshipGroupCode nvarchar(50),
@statuses nvarchar(350),
@studentSearch nvarchar(100),
@fromDateString nvarchar(50),
@toDateString nvarchar(50),
@studentTypeFilter nvarchar(50),
@offsetFrom INT,
@offsetTo INT,
@sortColumn nvarchar(50),
@sortOrder nvarchar(10),
@isExportToExcel BIT
AS
Begin
SET NOCOUNT ON;
-- variable declaration
declare @Separator varchar(1)= ','
DECLARE @XML XML
declare @studentType varchar(20)= null
IF(@studentSearch = '')
set @studentSearch = null
IF(@studentTypeFilter = 'new')
set @studentType = 'false'
else if(@studentTypeFilter = 'continuing')
set @studentType = 'true'
else set @studentType = null
if(@sortOrder = '' or @sortOrder is null)
set @sortOrder = ' DESC '
else if(@sortOrder = 'true')
set @sortOrder = 'ASC'
else
set @sortOrder = 'DESC'
-- accept comma separated parameters and convert them to use for in
clause condition
declare @tPsListViewStatus TABLE (val nvarchar(3600))
IF(@statuses is not null and @statuses<>'' and @statuses like '%,%')
BEGIN
set @statuses = replace(replace(replace(replace(@statuses,' , ',','),', ',','),' ,',','),' ','')
SET @XML = CAST( ('<i>' + REPLACE(@statuses, @Separator, '</i><i>') + '</i>') AS XML)
INSERT INTO @tPsListViewStatus
select t.i.value('.', 'VARCHAR(2000)')
FROM @XML.nodes('i') AS t(i)
WHERE t.i.value('.', 'VARCHAR(2000)') <> ''
END
select * from
(
select
(nm.LastName+' ' + nm.FirstName) as studentName,
pi.value,
ps.isContinuing,
sc.name as scholarshipName,
ps.status,
ps.CreatedDate,
ps.summary,
ps.effectiveDate,
ps.expiryDate,
sc.id as scholarshipId,
(SELECT STUFF((SELECT ',' + t.code FROM Academic_Term t WHERE t.effectiveDate >= ps.effectiveDate
and t.expiryDate <= ps.expiryDate and t.active = 'true' and t.code not like '%.%' and t.code not like '%COFA%'
and t.code not like '%NUR%' order BY t.code asc FOR XML PATH('')),1,1,'')) as code,
(SELECT SUM( convert( decimal( 10, 2 ), AmountAwarded )) as athleticAmount FROM profile_scholarship
WHERE (Status IN(select * from @tPsListViewStatus)) and id= ps.id) AS PS_amountAwarded,
(SELECT DISTINCT(fs1.code)
FROM profile_scholarship_term pst LEFT OUTER JOIN fund_source_academic_term fsat ON pst.FundSourceAcademicTermId=fsat.Id
LEFT OUTER JOIN fund_source fs1 ON fsat.FundSourceId=fs1.Id WHERE ps.Id = pst.ProfileScholarshipId) as fundSourceCode,
ps.studentProgramId,
ps.id as psId,
ROW_NUMBER() OVER(
PARTITION by ps.scholarshipId,
ps.profileId,
ps.status
ORDER BY
ps.EffectiveDate desc,
ps.ExpiryDate desc,
ps.CreatedDate desc
) AS RowNumber,
ps.profileId,
ps.FAStudentAcademicYearId
from
profile_scholarship ps
INNER JOIN scholarship sc on
ps.ScholarshipId = sc.id
INNER JOIN dbo.scholarship_sub_group ssg on
sc.ScholarshipSubGroupId = ssg.Id
INNER JOIN dbo.scholarship_group sg on
ssg.ScholarshipGroupId = sg.Id
INNER JOIN name nm on
ps.ProfileId = nm.profileId
INNER JOIN dbo.profile_identity pi on
ps.ProfileId = pi.ProfileId
where
sg.code = @scholarshipGroupCode and ps.SystemIdentified = 'SIS'
and (ps.Status IN(select * from @tPsListViewStatus))
AND((ps.status = 'DELETED' AND ps.summary like '%CVUE_DELETE%') OR (ps.status != 'DELETED'))
and pi.type = 'STUDENT_NUMBER'
and(ps.isContinuing IN(@studentType) or ISNULL(@studentType,'')= '')
and ps.CreatedDate BETWEEN @fromDateString AND @toDateString
) as resultList
where resultList.rowNumber = 1 and (ISNULL(@studentSearch,'')= '' or resultList.studentName like '%' + @studentSearch + '%'
or resultList.value like '%' + @studentSearch + '%')
order by
case
when @sortOrder <> 'DESC' then ''
when @sortColumn = 'modified' then resultList.CreatedDate end DESC,
case
when @sortOrder <> 'ASC' then ''
when @sortColumn = 'modified' then resultList.CreatedDate end ASC,
case
when @sortColumn = '' then resultList.CreatedDate end
DESC
OFFSET @offsetFrom ROWS FETCH NEXT @offsetTo ROWS ONLY
END
在这个查询中,如果@isExportToExcel为true,我想删除“OFFSET@offsetFrom ROWS FETCH NEXT@offsetTo ROWS ONLY”。@isExportToExcel的值是从java代码传递的。我尝试使用下面的案例陈述,但它不起作用
Case
when @isExportToExcel = 0 then 'OFFSET @offsetFrom ROWS FETCH NEXT @offsetTo ROWS ONLY' END
但这不起作用,我总是得到完整的数据,分页也不起作用
# 1 楼答案
不能使用内联大小写表达式来确定是否存在偏移量
我可以想出三种方法:
一种是使用动态sql编写整个查询
另一个是拆分逻辑:
第三个是基于布尔值重新填充参数: