如何将这个过程从SQL Server 2012转换到SQL Server 2008？

Question

我正在使用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;

- Ammar

---

CONCAT(a, ' ', b) == a + ' ' + b - Devart

SQL Server 2008 中的 fetch 也无法工作。 - Ammar

你需要使用一个带有IDENTITY_column的临时表，并结合SELECT TOP [@pageSize] ..和WHERE子句中的IDENTITY_column > [@Offset]来填充它。 - Mazhar

请问您能否将“@Mountaineer”更改为其他内容？ - Ammar

Answer 1

你可以在查询中即时生成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限制P.rn值的WHERE子句的位置。

- CHill60

Answer 2

以AdventureWorksLT2008R2数据库中的Product表为例

DECLARE @pageSize int = 10,
        @Offset int = 20

IF OBJECT_ID('tempdb..#xRecords') IS NOT NULL
    DROP TABLE #xRecords
CREATE TABLE #xRecords(
     ID             INT IDENTITY(1,1) CONSTRAINT PK_xRecords PRIMARY KEY
    ,ProductID      INT
    ,Name           NVARCHAR(50)
    ,ProductNumber  NVARCHAR(25)
)

INSERT INTO #xRecords ( ProductID, Name, ProductNumber)
SELECT 
     ProductID
    ,Name
    ,ProductNumber
FROM
    [AdventureWorksLT2008R2].[SalesLT].[Product]

SELECT
    TOP (@pageSize) *
FROM
    #xRecords
WHERE
    ID > @Offset

- Mazhar