Stored Procedure count of records only once

Maybe redesign your procedure to return two recordsets: the count and the subset of records (page). Something like this:

ALTER PROCEDURE [dbo].[usp_Test_Select]  
. . .  
   -- return the total number of rows  
   select count(*) from [dbo].[TestDB]  
   -- return the data  
   Select  [ID]  
                [Name],  
                [City],              
                [Year]  
                from [dbo].[TestDB]  
                where [ID] between ((@Poll) + 1) and (@Poll)*@count     

The caller code will need some simple adjustments to deal with two recordsets.

However, your core approach works only in case of “ideal” IDs, which start from 1 and do not have any gaps (that appear if you delete some rows, for example). This is not always possible. Consider the previous suggestions based on OFFSET or ROW_COUNT: https://learn.microsoft.com/answers/answers/85864/view.html, which can be adjusted to take into consideration the page size (@count).

Maybe notions like “pagination”, “page size” (instead of @count) and “page number” (replacing @joon ) better describe your needs.

     ALTER PROCEDURE [dbo].[usp_Test_Select]
          (  

            @Year int,
            @Poll int,
            @count int 
           )

     DECLARE @tblcnt int = (select count(*) from [dbo].[TestDB]

                  Select  [ID]
                 ,[ID]
                 ,[Name]
                 ,[City]            
                 ,[Year],
                 @tblcnt AS TotalRec 
                 from [dbo].[TestDB]
                 Where [ID] between ((@Poll) + 1) and (@Poll)*@count   

Yeah, you will get the rowcount on all rows, but what's the problem with that? (You can arrange to get it one row only, but frankly, it is not worth the complexity.(

CASE WHEN @joon = 0 OR [ID] = @joon (10-9) THEN (SELECT COUNT() FROM [dbo].[TestDB]) ELSE NULL END TotalRec

Hi @Rock Hitman ,

In your previous post, I have helped you solve this problem, and you have also checked that it is working. I wonder why you asked the same question.
I still give the same answer：

          ALTER PROCEDURE [dbo].[usp_GeicoData_Select]  
                (@poll int)  
           AS  
                Select  [ID]  
                       ,[VIN]  
                       ,[Make]  
                       ,[Model]  
                       ,[Year]  
            ,case when [ID]=@Poll*10-9 then (select count(*) from [dbo].[usp_GeicoData_Select] ) end Ycount  
                       FROM [TEST].[dbo].[Ins_Data]  
                       Where [ID] between @Poll*10-9 and @Poll*10  
                       ORDER BY [ID]  
                                 
          execute [dbo].[usp_GeicoData_Select] @poll=1    

Test code:

  alter procedure b_stu  
  (@poll int)  
  as  
  select EmpID,EmpName,SalaryCalc,case when EmpID=@Poll*10-9 then (select count(*) from dbo.Employee ) end Ycount    
  from dbo.employee   
  where  EmpID between @Poll*10-9 and @Poll*10   
       
  execute b_stu @poll=1  

In addition, the following code can display the count on only one line:

         ALTER PROCEDURE [dbo].[usp_Test_Select]  
              (    
              
                @Year int,  
                @Poll int,  
                @count int   
               )  
                   
                   
                      Select  [ID]  
                     ,[ID]  
                     ,[Name]  
                     ,[City]              
                     ,[Year]    
                     from [dbo].[TestDB]  
                     Where [ID] between ((@Poll) + 1) and (@Poll)*@count     
          union all  
                     select * from (select  01 [count1] ,02 [count2] ,'count' [count3],'count' [count4],  
                     case when [ID] = ((@Poll) + 1) then (select count(*) from [dbo].[TestDB] Where [ID] between ((@Poll) + 1) and (@Poll)*@count) end TotalRec     
                     from [dbo].[TestDB]) t  
                     where TotalRec>0  

Test code:

alter procedure b_stu  
(@poll int)  
as  
select EmpID,EmpName,SalaryCalc  
from dbo.employee   
where  EmpID between @Poll*10-9 and @Poll*10   
union all  
select * from (select  01 [count1] ,'count' [count2],  
case when EmpID=@Poll*10-9 then (select count(*) from dbo.Employee where EmpID between @Poll*10-9 and @Poll*10 ) end Ycount     
from dbo.Employee) t  
where Ycount>0  
  
execute b_stu @poll=1  

Best Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.