How to get Output parameter from a column in table for a stored procedure

I’ve stored procedure like this:

create procedure sp_testsp
(
@vc_order_by varchar(100),
@int_start_index INT,
@int_grid_size INT,
@count bigint output
)
as 
begin 

select * from 
(select ROW_NUMBER() over
(order by 
case @vc_order_by = '' then tab1.int_id end desc) AS row,
*,
COUNT(tab1.int_id) OVER() as totalRowCount 
from
(select * from tbl_test) tab1) tab2
where row BETWEEN CONVERT(VARCHAR, @int_start_index) and CONVERT(VARCHAR,(@int_start_index-1) + @int_grid_size);

set @count  = 0;

end

We can execute the above stored procedure by:

DECLARE @size bigint;
EXEC sp_testsp '', 1,5, @size output;
SELECT @size;

The written sp provides data based on pagination and we can retrieve 100 or any number of records by passing a number in @int_grid_size .

The table output looks like following:

row      int_id      vc_name    totalRowCount     
1          5            a            107
2          6            ab           107
3          7            abc          107
4          8            abcd         107
5          10           abcc         107

The last column gives the total records count of the table or total record if we use where condition.

I want to OUTPUT any one column value of the totalRowCount in ‘@count’ in the stored procedure.

I cannot use @@ROWCOUNT as it only sends the count of records the sp is outputting i.e in this case 5 but actual records are 107.

Just wondering if there is any way. Any help is apperciated. Thanks.

Edit:

I tried something like this, and it works:

create procedure sp_testsp
@param1 nvarchar(800),
@count bigint output
as 
begin 

select * from tbl_test tt where tt.col1 = @param1;

set @count = select Count(*) from tbl_test tt where tt.col1 = @param1;

end

The issue with this is I’ve to call the query once and then call the query again for @count. This is working but taking lot of time for big queries.

Answer

You can do that by temp table

 select * into #temp from 
     (select ROW_NUMBER() over
      (order by 
case @vc_order_by = '' then tab1.int_id end desc) AS row,
*,
COUNT(tab1.int_id) OVER() as totalRowCount 
from
(select * from tbl_test) tab1) tab2
where row BETWEEN CONVERT(VARCHAR, @int_start_index) and CONVERT(VARCHAR,(@int_start_index-1) + @int_grid_size);

select top 1 @count=totalRowCount from #temp

select * from #temp --you can exclude totalRowCount

Advertisement

Answer