SQL Server procedure design to get results and results count in a single call

Question

I have a requirement where I need to pull the top 100 of the data result set and also the count of the records of the total data which satisfy my filtering condition.

Thanks, Manoj.

Answer 1

You can use an output parameter to return the total number of records and also a select statement that returns a result set.

For example:

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

If you need to call it from code, here's a Java example to illustrate how you would make the call:

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");
}

This method is just an example to show you how to make the call. Don't write production code like this!!!