我在生产中使用sql server 2008。下面是我的存储过程,该存储过程在SQL Server 2012上运行良好,但在SQL Server 2008中给出了提取错误。原因是fetch
不是在SQL Server 2008中内置的,但在SQL Server 2012中可用。请帮助我进行转换脚本以与SQL Server 2008一起使用。
Create PROCEDURE sp_LoadSponsorsListofDonorsforReminder
@pageSize int,
@Offset int,
@TotalRecords int out
as
BEGIN
SELECT max(cd.OID) as OID, max(cd.DonationId) as DonationId,
max(cd.DonorId) as DonorId,
max(Concat(do.FIRSTNAME,' ', do.LASTNAME)) as Donor_Name,
max(do.PHONENUMBER) as PHONENUMBER,
max(do.MOBILENUMBER) as MOBILENUMBER, max(do.ADDRESS) as ADDRESS,
max(do.EMAIL) as EMAIL, max(cd.PaidUpTo) as PaidUpTo,
max(cd.StartDate) as StartDate, max(ca.ChildCode) as ChildCode,
max(concat (ca.FirstName,' ', ca.LastName)) as Child_Name,
max(org.ORGANIZATION_NAME) as Village,
max(d.DonationDate) as DonationDate,
max(r.ReminderOneDate) as ReminderOneDate
FROM child_sponsorship cd
inner join donations d
ON cd.DonationId = d.OID
inner JOIN donor do
ON cd.DonorId = do.OID
inner join child_admission ca
ON cd.ChildId = ca.OID
inner join organization org
ON do.ORGANIZATION = org.OID
left join Reminder_Information r
ON cd.DonorId = r.DonorId
WHERE d.DonationDate < DATEADD(day, -365, GETDATE()) AND
cd.DonorId <> 1174 AND
cd.DonorId <> 1175
GROUP by cd.childId
ORDER By Max(d.DonationDate), max(cd.DonorId) desc
OFFSET @Offset ROWS
FETCH NEXT @pageSize ROWS ONLY
SET @TotalRecords = (select count(*) from child_sponsorship WHERE 1=1);
END;
最佳答案
您可以为查询动态生成ROW_NUMBER。我不会在这里重现整个内容,但这是一个使用Northwind示例数据库中的Products表的简单示例
DECLARE @pageNumber INT = 2, @pageSize INT = 6
SELECT ProductID,ProductName, UnitPrice
FROM
(
SELECT ProductID,ProductName, UnitPrice,
ROW_NUMBER() OVER (ORDER BY ProductID) AS rn
FROM Products
) AS P
WHERE P.rn BETWEEN ((@pageNumber-1)*@pageSize)+1 AND @pageSize*(@PageNumber)
内部查询产生行号,外部查询获得您想要的字段(即没有行号)。内部查询是将所有内容放在上面的位置
外部查询是您使用AND放置WHERE子句以限制P.rn的值的地方
关于sql-server - 如何将此过程从SQL Server 2012更改为SQL Server 2008,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/36843134/