我在生产中使用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/

10-14 16:07
查看更多