我如何限制在我的SQL Server函数返回的行数?

Question

CREATE FUNCTION [dbo].[GET_CUSTOMER_DATA]  
(   
-- Add the parameters for the function here
@customerID bigint,
@maxRows int,
@offset int,
@rows int
)
RETURNS TABLE 
AS
RETURN 
(
-- Add the SELECT statement with parameter references here
SELECT *
FROM SS_CustomerCard AS SS_CC 
INNER JOIN SS_PersonalRepresentative AS SS_PR
ON SS_PR.customerID = SS_CC.ID
INNER JOIN SS_ApplicationStatus AS SS_AS
ON SS_AS.CustomerID = SS_CC.ID
WHERE
SS_CC.ID='@customerID'
ORDER BY SS_AS.EventDateTime DESC, SS_CC.FirstName DESC, SS_CC.LastName DESC
OFFSET @offset ROWS
FETCH NEXT @rows ROWS ONLY
)
GO

我想传递一个参数来限制返回的行数(maxRows)

Answer 1

可以在SELECT语句中使用TOP()子句。

这是一个概念性的例子。

/p>

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, vehicleMake VARCHAR(20));
INSERT INTO @tbl (vehicleMake) VALUES
('Chevrolet'),
('Tesla'),
('Audi'),
('Nissan');
DECLARE @tableVariable TABLE (ID INT, vehicleMake VARCHAR(20));
DECLARE @topRows INT = 2;
INSERT INTO @tableVariable
SELECT * 
FROM @tbl
ORDER BY id;
SELECT TOP(@topRows) *
FROM @tableVariable
ORDER BY id;

Answer 2

我认为您试图限制OFFSET/FETCH之前的行总数。如果是这样，则需要将其放在子查询/派生表中，并添加另一个ORDER BY:

CREATE FUNCTION [dbo].[GET_CUSTOMER_DATA]  
(   
-- Add the parameters for the function here
@customerID bigint,
@maxRows int,
@offset int,
@rows int
)
RETURNS TABLE 
AS
RETURN 
(
SELECT *
FROM (
SELECT TOP (@maxRows) all_needed_columns_here_aliased_if_necessary
FROM SS_CustomerCard AS SS_CC 
INNER JOIN SS_PersonalRepresentative AS SS_PR
ON SS_PR.customerID = SS_CC.ID
INNER JOIN SS_ApplicationStatus AS SS_AS
ON SS_AS.CustomerID = SS_CC.ID
WHERE
SS_CC.ID=@customerID
ORDER BY SS_AS.EventDateTime DESC, SS_CC.FirstName DESC, SS_CC.LastName DESC
) t
ORDER BY EventDateTime DESC, FirstName DESC, LastName DESC
OFFSET @offset ROWS
FETCH NEXT @rows ROWS ONLY
)
GO