如何将此过程从SQL Server 2012更改为SQL Server 2008

问题描述：

我正在使用sql server 2008进行生产。下面是我在SQL Server 2012上正常工作的存储过程，但在SQL Server 2008中给出了一个提取错误。原因是fetch不是在SQL Server 2008中构建的，而是在SQL Server 2012中可用。帮助我将此脚本转换为使用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;

'CONCAT（一， ''，b）== A + '' + B' – Devart

也取SQL Server 2008中不工作 – Ammar

您需要填充一个临时表与IDENTITY_column和使用SELECT的组合TOP [@pageSize] ..并使用IDENTITY_column> [@Offset]。在WHERE子句 –

答

您可以为查询动态生成ROW_NUMBER。我不打算在这里重现了整个事情，但这里是一个使用Products表从Northwind示例数据库

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)

内查询产生的行数个简单的例子，外部查询得到的字段，你想要（即没有行号）。内部查询是你把所有你有以上的东西，外部查询是你的WHERE子句使用并限制P.rn

答

的价值运用AdventureWorksLT2008R2 Product表DB作为一个例子把你

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

如何将此过程从SQL Server 2012更改为SQL Server 2008

相关推荐