sql-server - SQL Server 过程设计可在一次调用中获取结果和结果计数

Question

我有一个需求，需要拉取数据结果集中的前100条，以及总数据中满足我的过滤条件的记录数。

谢谢，马诺杰。

Answer 1

您可以使用输出参数返回记录总数，也可以使用返回结果集的 select 语句。

例如:

create procedure test
   @totalCount int output
 as begin
     select * from something -- your select statement goes here
     select @totalCount = 'write your statement that counts the records.'
 end

如果您需要从代码中调用它，这里有一个 Java 示例来说明如何进行调用:

public void test(Connection con) throws SQLException{
    CallableStatement cs = con.prepareCall("test");
    //you can set more parameters if you need to, i.e.: cs.setInt(0,id);

    //you need to register your output parameter. If you are referencing it by name, it should match with the name given in the stored procedure.
    cs.registerOutputParameter("totalCount", Types.INTEGER);
    ResultSet rs = cs.executeQuery();
    while(rs.next()){
       //you can save the data into a data structure; a list for example.
       //Or just print the records.
    }
    //here's how you can get the total count
    int total = cs.getInt("totalCount");
}

此方法只是向您展示如何进行调用的示例。不要这样编写生产代码!!!