java - T-SQL 存储过程不会通过 COUNT(*) 在空 SELECT 结果上返回零

Question

我正在使用 Spring Framework 的 StoredProcedure(当然，我正在扩展它)来获取结果集和输出参数 (@totalRowsReturned)，它是一个整数.问题是，当返回的结果集应该是一个空列表时，当我尝试检索输出参数(totalRows，天真地我希望它为零)时，我得到了一个 NullPointerException .

我想提一下，当找到的结果集不为空时，代码可以正常工作。

我的问题是:

1. 在这种情况下，为什么不将 @totalRowsReturned 设置为零？ (或者如果是，为什么我不能通过 Java 代码检索它？)

2. 我怎样才能使这段代码(Java 代码 + T-SQL 代码)以 @totalRowsReturned 将在需要时设置为零的方式工作，并且我可以通过以下方式检索它Java 代码？

道:

List<Book> books = null;
int totalRows = 0;

Map<String, Object> results = storedProcedure.execute(parameters);
books = (List<Book>) results.get("rs");
totalRows = (Integer) results.get("totalRowsReturned"); // NullPointerException on this line if total rows are supposed to be zero!!

T-SQL 存储过程:

CREATE PROCEDURE Find_Books

    @authorName Varchar(250),   
    @totalRowsReturned INTEGER OUTPUT

AS

BEGIN

    SET NOCOUNT ON;
    DECLARE @SelectQuery NVARCHAR(2000)

    SET @SelectQuery = 'SELECT @totalRows=COUNT(*) OVER() FROM book b WHERE b.author_name = @authorName'

    Execute sp_Executesql @SelectQuery, N'@authorName VARCHAR(250), @totalRows int OUTPUT', @authorName, @totalRows=@totalRowsReturned OUTPUT

-- Select resultset goes here...

END

更新:

实际上，我的存储过程看起来更像这样(变化是在 SELECT 中增加了 @first_id = b.book_id):

CREATE PROCEDURE Find_Books

    @authorName Varchar(250),   
    @totalRowsReturned INTEGER OUTPUT

AS

BEGIN

    SET NOCOUNT ON;
    DECLARE @SelectQuery NVARCHAR(2000)

    DECLARE @first_id int
    DECLARE @first_id_local_returned int

    SET @SelectQuery = 'SELECT @first_id = b.book_id, @totalRows=COUNT(*) OVER() FROM book b WHERE b.author_name = @authorName'

    Execute sp_Executesql @SelectQuery, N'@authorName VARCHAR(250), @first_id int OUTPUT, @totalRows int OUTPUT', @authorName, @first_id=@first_id_local_returned OUTPUT, @totalRows=@totalRowsReturned OUTPUT

    -- Select resultset goes here... I am using the value of @first_id_local_returned in this SELECT...

END

问题是，当 SELECT 没有返回任何行时，b.book_id 未定义，所以我得到一个 org.springframework.dao.TransientDataAccessResourceException ... Column 'book.book_id ' 在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中。

所以看起来如果我保留 OVER()，那么 @totalRows=COUNT(*) OVER() 会在返回零行时失败，如果我删除了 OVER()，然后 @first_id = b.book_id 失败了。

知道我是如何克服这个问题的吗？

Answer 1

COUNT(*) OVER ()在这里使用不正确。只需使用 COUNT(*)

COUNT(*) OVER ()返回与 COUNT 一样多行的结果集.例如，如果结果是 3，则结果集将是

3
3
3

您查询的效果是重复重新分配值 3到 @totalRows变量与行数一样多，这是完全没有意义的。

反之如果COUNT(*) = 0然后是 COUNT(*) OVER ()结果集为空，因此根本不会分配给您的变量。

COUNT(*)将始终在此处为您提供单行标量结果集，您可以将其分配给变量，并且将拥有更高效的执行计划，而无需不必要的公共(public)子表达式假脱机。

编辑

回复您在评论中提出的问题。这与您的链接文章的作用相同。它可以使用更窄的索引来查找(比如说)第 10,000 名员工，然后仅针对后续的 1 页记录加入部门。这种分页方法只能正常工作，因为每个员工只有一个部门。

WITH E1(RN, EmployeeID)
     AS (SELECT ROW_NUMBER() OVER (ORDER BY EmployeeID),
                EmployeeID
         FROM   Employees)
SELECT TOP (@maximumRows) e.*,
                          d.Name AS DepartmentName
FROM   Employees e
       INNER JOIN Departments d
         ON e.DepartmentID = d.DepartmentID
WHERE  EmployeeID >= (SELECT EmployeeID
                      FROM   E1
                      WHERE  RN = @startRowIndex)
ORDER  BY e.EmployeeID